Date criteria in query

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

Guest

I seem to have a problem with a make-table query where I'm trying to extract
the previous five years worth of data. The criteria is based on the field
"Create_Date" and I put this in the criteria row:
=DateAdd("yyyy",-5,Date())
Click to expand...

There's no record for exactly five years ago, but there's 607 records in the
table and the query gets 402 of them. When I go to the table and sort in
ascending order by that date, the last one "before 5 years ago" is record no.
208. That leaves 3 records unaccounted for. Does this seem strange to you?
Shouldn't there be 399 records in the result set?

Thanks in advance
 
Several possible issues.
#1 is most likely.

1. Nulls
Are there any rows that don't have a date? They will not be returned. See:
The Query Lost My Records!
at:
http://members.iinet.net.au/~allenbrowne/casu-02.html

2. Time Component
If the records have a time (as well as a date), it affects their selection
(though probably not in the example you gave).

3. Corrupted index
If neither of the first 2 explain what you are seeing and the date field is
indexed, see the 3rd symptom in:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html
 
Back
Top