Query with a date range does not return all dates

R

Rebekah

I have a query that I have been using for the past year for a report that looks at transactions that happened between a date range. Up until now, the query has worked but for some reason, when I enter the dates for the month of April it will only return data from April 1 to April 29, it doesn't pick up any of the transactions that occured on April 30. Any suggestions about why it is not picking this up?

Thank you.
 
G

Graeme Richardson

Not all of these will be relevant.

Is there data for April 30?
Check data type of field (should beDate/Time)
Use 4 digit year (have you changed the format of the entered date in e.g.
dd/mm/yy to d/m/yy)
What does the SQL Where clause look like? Are you using Between or >= and <=
How are parameters passed to query? If query looks at a field, check that
it's format is Date/Time
Check System Date format on the computer is consistent.

Cheers, Graeme
 
J

John Spencer (MVP)

AND ONE that Graeme left out.

Check to make sure the date is not including a time. For instance

Between #4/1/04# and #4/30/04# will return all records from Midnight of the 1st
to Midnight of the 30th. So if there is a time, the only records you will get
for the 30th are those that occurred at exactly midnight.

One way to check this is to use

DateField >= #4/1/04# and DateField < #5/1/04#

If this returns the expected set of records, you are almost guaranteed to have a
time in your date time field along with the date.
 
Top