SQL query for date

[Johnkokk]

I have a field in an access database called day and is declared as DATE/TIME I want to find through SQL all records with dates between 01/03/2010 and 15/03/2010 When i sent the sql command 'Select * from journal1 where day between 01/03/2010 and 15/03/2010' it does not work. Even if i try 'Select * from journal1 where date(day) between 01/03/2010 and 15/03/2010' it doesn't work. I am almost sure that the problem is because the day includes also a time portion in it, but if i put date(day) shouldn't this be working ? Thanks.

[loyal ginger]

Use # to delimit date:

Select * from journal1 where day between #01/03/2010# and #15/03/2010#

However, I am not sure how to tell Access if it is mm/dd/yyyy or dd/mm/yyyy. If you can use a parameterized query command, this will be easier.

[Corporal Agarn]

If the column name is "day" you will most likely need brackets as in [day].

[Johnkokk]

Well using # worked, but actually it expects the date to be completely reversed. So if i use 'select * from journal1 where day>#15/03/2010#' it doesn't get any results, but if i use 'select * from journal1 where day>#2010/03/15#' it is working. Is it possible to change this ? Thanks.