Date Between Ranges for SQL Query

[BigSloppyJoes2000]

Hello pals, hope you're all having a swell 'ol day. I am having trouble with using a between range for my dates in my sql queries. My SQL for this query is like:




SELECT *
From Table_Name
WHERE Date BETWEEN #01/01/2006# AND #01/02/2006#;


If I have different years, so, 2006 and 2007, it works. And if I'm just comparing days, so, like, 01/01/2006 and 31/01/2006 it works. But for some reason, if I try between two months, it just will not work. I get errors, and it's really confusing me. My data type is short-date. Can anybody help me out of this pickle?

[ItsMe]

Should be working. Try
WHERE [Date] BETWEEN #01/01/2006# AND #01/02/2006#;


Date is a reserved word and should not be used for field names, or any other name for that matter. The brackets may reduce any confusion Access is having with the SQL statement.

[BigSloppyJoes2000]

its not Date, that was just an example. It's working if there are different years, or different day values, just not wit the months. I really don't understand how my SQL is wrong...

[ItsMe]

The only other thing I can think of would be that your not using the American date format and Access is not interpreting the correct month/day.

[BigSloppyJoes2000]

The only other thing I can think of would be that your not using the American date format and Access is not interpreting the correct month/day.

​I'm using a UK version of access, so the date format it uses would be dd/mm/yy, i'm pretty sure anyway.

[ItsMe]

Access does not see dates that way.

Access interprets date fields as mm/dd/yyyy

[BigSloppyJoes2000]

Access does not see dates that way.

Access interprets date fields as mm/dd/yyyy

Sorry I mean I'm doing it with yyyy

[June7]

Post your complete sql statement.

Review http://allenbrowne.com/ser-36.html