Access Query: Return all records that occur on a certain day, ignoring the time

I have an Invoices table with a Date/Time field. I want to write a query that returns all invoices for a certain day.

The problem I'm having is that when I filter the query by a particular date (such as 04/30/2011), no records are being returned. My suspicion is that Access is trying to return records that are an EXACT MATCH开发者_JS百科 of 04/30/2011.

What I want is a query that returns all invoices that occur ON 04/30/2011 regardless of what time they occur. Basically, I want Access to care about the day but not the time.

Is there any documentation on how to do this? I sure can't find any. I'm using Access 2010. Thanks!

Without seeing your attempt, it's hard to say what you're doing wrong. I imagine you're trying to check for equality, which basically will mean that it must have happened exactly at midnight.

SELECT * FROM table WHERE date >= #04/30/2011# AND date < #05/01/2011#

It is about the datatype, I assume your date is stored in a datetime field. This will store the time as well and if no time is specified will default to midnight. I ususally use the BETWEEN function to do this:

SELECT *
FROM table
WHERE datefield BETWEEN '2011-04-30' AND '2011-05-01'

Because both dates will be at midnight, you will get the results from April 30.

You are correct about what Access is doing; when you supply a datetime value with no time, it assumes midnight. So "2011-04-30" means "2011-04-30 00:00:00".

There is a Datediff function in Access that's used like this:

Datediff("d", "2011-04-30", [FieldName]) = 0

You could try something like:

DateAdd("d", DateDiff("d", 0, MyDate), 0 )

Another choice:

CDate(Format(MyDate,"yyyy-mm-dd"))

Another variant of solutions mentioned by others that allows you to plug in a single date:

DateCol >= #2011-04-30# And DateCol <= DateAdd("d",1,#2011-04-30#)