Simple query question on Date/Time

[colintis]

I'm fixing a query that takes date filter on specific date (e.g. 10/13/2010). The date field in the table source also contains time along with the date. (E.g. 13/10/2010 6:26:45 AM)

On the where clause of the query, how can I filter the date with the standard short date format? I tried 2 of the followings but didn't work.

Method 1: WHERE Date = #10/13/2010#
Method 2: WHERE Date BETWEEN #10/13/2010# AND #10/13/2010#

[colintis]

Just used a longer method to solve this matter, I would appreciate to find a solution that is shorter than this.

WHERE Date BETWEEN #10/13/2010 00:00:00# AND #10/13/2010 23:59:59#

[MMcCarthy]

Simple answer is ... if you don't specify a time then Access assumes a time of 00:00 so when you say between 10/13/2010 and 10/13/2010 it assumes you mean between 10/13/2010 00:00 and 10/13/2010 00:00 so you get no results. The only two options are either to do what you are doing and give the times or to set the second date to be the day after. In other words to say between 10/13/2010 and 10/14/2010 which since it assumes a time of 00:00 for both dates will only return for the 10/13/2010.

Hope that makes sense :)

Mary

[colintis]

Yep, that makes sense to me, but would be it possible that the time at 10/14/2010 00:00:00 also included in the between result? I know that the chance of getting exactly that time is nearly impossible, but still curious to know. :P

[MMcCarthy]

I don't think so but you would have to test it to be sure. Set up a dummy record with that date and time and see if it shows up in the results.

[NeoPa]

I think you're looking for DateValue() :

WHERE (DateValue(Date) = #10/13/2010#)

[colintis]

Beautiful NeoPa, thank you :D

[NeoPa]

Always a pleasure mate :-)