Compare dates

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am having trouble comparing dates for some reason. Here
is what the WHERE clause looks like in my sql statement:
"WHERE tblStudents.DateEntered >= #" & rDate & "#"
Both fields are stored in the British format (dd/mm/yyyy)
and rDate is a date which is read from a text file. The
query seems to think that 13/09/2003 os greater than
01/10/2003.
I have tried to use the Format(rDate,"dd/mmm/yyyy")
function to be sure the date is returned in British
format and that it sees the month as a month and not a
day, but it comes back the same.
Any ideas?
Thanks in advance.
Tom
 
Date literals (that is, enclosed in # delimiters) in SQL statements are
interpreted according to the US date format (that is, "mm/dd/yyyy"),
regardless of your regional settings.

13/09/2003 is not a valid date in this format. Access (actually Jet) will
detect this, and will correctly (at least as far as you're concerned)
interpret the date as September 13, 2003.

01/10/2003 is a valid date in the US date format, and Jet will interpret the
date accordingly as January 10, 2003, which is less.

You can reduce the potential for these (and other) kinds of problems by
using a parameter query with a Date/Time type parameter instead of a date
literal. If you do use a date literal, you need to format it to US date
format.
 
Date literals in JET SQL must be in the format "#mm/dd/yyyy#".

Try:

....WHERE tblStudents.DateEntered >= Format(rDate, "\#mm/dd/yyyy\#")
 
Back
Top