Hi,

Can anybody help me do an sql query on a table using the datetime format?

I have tried several methods but cannot get any to find the required
record.

An entry is time stamped using VB5.0 Now statement which returns a double.

I can create the record and if I view it with Access 7.0 or VisData the
field correctly shows the date time of the record.

However, when I try to search for the record It never finds it. The SQl
query looks like...

sql$="select * from CallHistory where datetime="+trim$( str$( CallStart# ))

I tried Converting the date format to the same as displayed by
Access/VisData but the SQL didn't like the space in between the date /
time, ie...

sql$="select * from CallHistory where datetime="+format$( CallStart#
,"dd/mm/yy hh:mm:ss")

Putting the date in quotes the reported a type mis-match.

I also tried changing the field type to a double and although the value was
exactly the same in the table and the query it still didn't find it.

Please help as I'm rapidly running out of hair!!!!

Cesare

p.s. Thanks to anybody who replies.

You have to insert # before and after your date, and also it must be in
yy/mm/dd format - like this:

sql$="select * from CallHistory where datetime=#" & format$( CallStart#
,"yy/mm/dd hh:mm:ss)" & "#"

Cheers,

Dan