Problem with trying to date ranges using BETWEEN...AND...

[catchy]

Hi there,

I'm tearing my hair out over a simple issue, hoping someone can help: I have a JetSQL stmt where the BETWEEN...AND... just won't work.

I have registration records between 8/08/2005 and 11/08/2005, and I want to be able to do date ranges. BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #9/08/2005# AND #11/08/2005#" I get no results.

BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #8/08/2005# AND #10/08/2005#", it spits out all the registrations, even ones with a createdate of 11/08/2005!

What gives? What am I missing? Any wisdom, HUGELY appreciated! :)

[miranda]

it looks like you are defining dates as dd/mm/yyyy and the database is using mm/dd/yyyy.

[Bullschmidt]

Hi catchy and welcome to the board!

I agree with miranda and so perhaps change this:

"SELECT * FROM registrations WHERE createdate BETWEEN #9/08/2005# AND #11/08/2005#"

To be more like this instead:

"SELECT * FROM registrations WHERE createdate BETWEEN #" & DateSerial(2005, 8, 9) & "# AND #" & DateSerial(2005, 8, 11) & "#"

And the DateSerial function's arguments are year, month, day.

And FYI here's an example setting a date range based on posted fields:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

And here's an example setting a date range to be the current and previous months:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"