SQL "Less than" use with dates

[lfirth]

I'm trying to execute the following SQL with no success.

"SELECT * FROM tblInspectors WHERE fldPIExp >= " & datDate & ""

fldPIExp is a date value and so is (you guessed it) the variable datDate. The ruturn still has dates that are less than datDate. Any ideas?

[lfirth]

OK, I'm going to reply to my own post here, I forgot the "#" in the SQL:

"SELECT * FROM tblInspectors WHERE fldPIExp >= # " & datDate & " # "

But it still doesn't work properly. It seems to only look at the year part of the date values.

[wmif]

whats the value of that variable?

[lfirth]

The variable is: 12/05/2005 that is Aussie time i.e. 12th May 2005. It only returns date in 2006. I wonder if it has anything to do with my regional settings? They are set correctly (to Aussie Dates) but maybe the SQL doesn't recognise this and thinks I'm asking about 5th of December 2005 which would explain why I only get dates in 2006 returned.

[wmif]

ive not dealt with this personally, but ive seen regional settings cause problems like this before on this site.

[lfirth]

Done some experimenting and it does seem to be a regional date setting problem. If I format my date value (datDate) as a string in the "mm/dd/yyyy" format first like so:

datDate = DatePart("m",datDate) & "/" & DatePart("d",datDate) & "/" & DatePart("yyyy",datDate)

Then when I execute the SQL I get the desired results.

Anybody know a better way?

[buntine]

Use the DataDiff function. Logical operators won't work when dealing with dates in most databases.

Here is an example: http://www.techonthenet.com/access/functions/date/datediff.htm

Regards.

[wmif]

the datediff function can get confused with regional settings as well. just be aware of that if you use it.

[jazzy639]

This is what I use and it works great: DATE()

So your new SQL query would be:

SELECT * FROM tblInspectors WHERE fldPIExp >= DATE()

Hope this helps!

Jazzy