i monday Access Date issue...

[justinpl]

I created a SELECT statement that querys an Access database and having a problem with the Date field. When the Access field data type is Date/Time set to Short Date I am having problems selecting any of the dates. Example: I do a "SELECT * FROM Corrective_Data WHERE Date = " & RequestDate and it does not pull any data. Then I change the Access field data type to TEXT and then do "SELECT * FROM Corrective_Data WHERE Date = '" & RequestDate & "'" and it works fine.

The problem is when I do an Order By Date at the end of the Select statement the Ordering process is different between text values and date values. Example Data type text would say that 12/31/2002 is greater than 1/1/2003 because of its numberic value, where the Date format know that 1/1/2003 is greater than 12/31/2002.

Let me know if anyone has any suggestions...

 

[justinpl]

i meant to say A Monday Access Date Issue...

 

[GaryC123]

Access stores dates in mm/dd/yyyy format so when inserting or selecting based on dates you have to use that format or yyy/mm/dd. dd/mm/yyyy will fail when the day is 13 or greater or will give you incorrect results.

http://www.intouchmedialtd.com