Date criteria in query

[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())

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

 

[Allen Browne]

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