SQL Date Headaches

[Douglas J. Steele]

That's correct, and as I think I said earlier, it's because there isn't a
20th month, so Access knows that 20/02/2008 is 20 Feb, 2008. However,
03/02/2008 will ALWAYS be treated as 02 Mar, 2008 in a query, even though
you mean that to be 03 Feb, 2008. On a form, how 03/02/2008 gets recognized
depends onthe user's Short Date format as set through the Control Panel.

 

[Douglas J. Steele]

It would appear that Flash is the issue.

Is there a Flash forum similar to this to which you can post? (I know there
isn't one here on the Microsoft server)

 

[John W. Vinson]

Thanks, Im understanding date is not a date its a number, Im not sure how to
progress this anymore ...

This SQL runs in MS access ....

UPDATE UserOverallResults
SET Results = 30
WHERE UserName = 'Rov'
AND format(QuizDate,("mm/dd/yyyy hh:nn:ss")) = #20/02/2008 15:46:38#

which Runs but UPDATES 0 Rows.

Since the Format() function returns a String value, not a date, try

UPDATE UserOverallResults
SET Results = 30
WHERE UserName = 'Rov'
AND format(QuizDate,("mm/dd/yyyy hh:nn:ss")) = "20/02/2008 15:46:38"

That's comparing a formatted string to a formatted string, which should work
(and will also remove any concern about sub-second roundoff error).

 

[Rob W]

It was a flash issue, this code worked in the end.
Extremely happy, thanks to all those who helped me out, I did understand
alot more about dates in general after. Thanks, appreciated.

mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " +
UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate =
#"+displaydate+"# + TimeValue('"+timedisplay+"')");