Searchin & comparing dates

[sterankin]

In my access database there are several entries with different dates.

I want to be able to search for entries that have a date within a week from todays date.

I take todays date from the system clock, and add 7 to it (a week), and compare search the database for any dates <= that date.......but its not working.

I am getting dates which come AFTER the date I am looking for!

In access the date format is short date, i.e. 05/07/02

here is a code snippit

theDate = theDate + 7

Dim strQueryx2 As String

strQueryx2 = "SELECT * FROM people where ExpiryDate <= #" & Format(theDate, "dd/mm/yyyy") & "# "

I read somewhere that access uses the american format in a date, it could be that?

I am using the english format, i.e. 07/06/02 reads 7th/june/2002

Thank folks.........

[Ed Lampman]

Try it without formatting the date. Although the date appears to be formatted in Access, it's still being stored as an 8 byte numeric value.

theDate = theDate + 7

Dim strQueryx2 As String

strQueryx2 = "SELECT * FROM people where ExpiryDate <= " & theDate