Query with a date range does not return all dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
Back
Top