Date Difference Ignoring Year

[JoeF]

I am trying to write a query where I only want to pick records prior to the current date (now()). If I do the query criteria as follows:
<#1/25/01#
then I am getting records with dates from early january in 2001, 2000, and 1999, but I am not getting records from feb to dec of 1999 or 2000. Why does the formula not acknowledge the year in my statement?

Also, if I want to get records from no longer than two years ago, can I just subtract out the days, as follows?
>now()-(2*365)
This second statement also would require me getting the year acknowledged.

 

[JerryDennison]

You've probably excluded the year in the field you've set the criteria for. You should make sure that both the field being returned and the criteria's formats match. In this case the format is (roughly) Short Date. (technically speaking short date is mm/dd/yyyy but Access should parse it correctly the way you have it). So you should force the field to be short date as well: Format([FieldName], &quot;Short Date&quot

You can do exactly what you've described in your second paragraph to get everything from 2 years back from today. Again, make sure you format both sides of the equation(the field and the criteria):

>Format(Now()-(2*365), &quot;Short Date&quot