CDate (Data type conversion error. (Error 3421)) 2

[mrgulic]

Set rst2 = dbs.OpenRecordset("SELECT Date, tblFEC.Start, tblFEC.End, tblFEC.Platform, tblFEC.Description, tblFEC.Case FROM tblFEC ORDER BY tblFEC.Start, CDate(Right([date],Len([date])-InStr([date],", "))); 'ORDER BY tblFEC.Start;")

The above table (tblFEC) is a linked HTML page, The date field looks like the following (D.O.W., Month Day, Year)

Example: Friday, Febuary 24, 2006.

In order to do any sorting I have to convert the date field but I get the "Data type conversion error".

Any ideas?


No I can't get the owners to change it at the source either.

 

[stix4t2]

instr syntax
instr( pos, str, findstr)

 

[Remou]

How about:
CDate(Mid([DateField],InStr([DateField],",")+1))

 

[PHV]

Febuary is not a valid month name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrgulic]

stix42: Sorry, there is nothing wrong with the MID or INSTR, they work just fine.


Remou: what does the +1 do?


PHV: What is wrong with Febuary as a month? I know that you can not sort with date like "Friday, Febuary 24, 2006"


The CDate(Right([date],Len([date])-InStr([date],", "))) converts it just fine but when you run it in a query but the moment you make the sql string a recordset (as below) I start to see the error.

 

[PHV]

What about Feb[!]r[/!]uary instead ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

The instr gets you to the end of the comma, +1 gets you past the space after the comma.

 

[mrgulic]

OK, I guess I am not making sense.

The following SQL works fine in a query:

SELECT Date, tblFEC.Start, tblFEC.End, tblFEC.Platform, tblFEC.Description, tblFEC.Case FROM tblFEC ORDER BY tblFEC.Start, CDate(Right([date],Len([date])-InStr([date],", "))) ORDER BY tblFEC.Start;

The problem o seems toccurs when you put the SQL code in here and make is a recodset.

[COLOR=red]Dim rst2 As Recordset          
Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("[/color]SELECT Date, tblFEC.Start, tblFEC.End, tblFEC.Platform, tblFEC.Description, tblFEC.Case FROM tblFEC ORDER BY tblFEC.Start, CDate(Right([date],Len([date])-InStr([date],", "))) ORDER BY tblFEC.Start;[COLOR=red]")[/color]

 

[PHV]

Set rst2 = dbs.OpenRecordset("SELECT Date, tblFEC.Start, tblFEC.End, tblFEC.Platform, tblFEC.Description, tblFEC.Case FROM tblFEC ORDER BY tblFEC.Start, CDate(Right([date],Len([date])-InStr([date],[!]'[/!], [!]'[/!]))) ORDER BY tblFEC.Start;")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eti3nne]

I am trying to do something similar (produce age from dates) but SQL won't accept CDate or InStr as a 'valid function name'!

 

[mrgulic]

eti3nne

Make sure you have "Visual Basic For Applications" added. In mine it is the first reference.

BTW: It was those frackin double quotes. I always forget to change them to single in a sql statement.
Thanks PHV