Access/Jet SQL: Date/time issues

[Robtyketto]

Greetings,

I'm using Flash MX connecting to an MS Access database via JET.

I've stored dates using Flash/Jet into my database as 'dd/mm/yyyy hh:mm:ss' into a DATE/TIME field.

However I can't update because of the WHERE clause as I am not specifying the date correctly. I believe syntax wise I need to use # and specify it in american format mm/dd/yyyy hh:mm:ss.

I have the code below working :-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate > #02/19/2008#");

When I add the time it complains and using the "=" clause it complains :-

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 WHERE UserName = 'Rob' AND quizDate = #02/19/2008 12:03:32# ");

Can anyone please assist, ive spent hours messing around with date/time formats, very confused.

Thanks
Rob W

[Robtyketto]

After further testing it appears not to like the time format as the code below works

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = 10 
WHERE UserName = 'Rob' AND quizDate = #19/02/2008#");

As soon as I add time (hh:mm:ss) it errors.

[peter_budo]

Well, looks like you got your solution http://www.eggheadcafe.com/software/aspnet/31865269/sql-date-headaches.aspx
Or isn't that you? :D