Why does select criteria by date range ignore year?

[Guest]

My query uses Between [date1] and [date2] as a selection criteria on a date
field. When I enter a date range (04/04/05-04/30/05) I get all records for
April 2005 and April 2004

 

[Graham R Seach]

Pete,

Use the DateSerial() function in combination with the BETWEEN operator.

SELECT x, y FROM Table1
WHERE z BETWEEN DateSerial(1956,11,5) AND DateSerial(2005,1,1)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[John Vinson]

My query uses Between [date1] and [date2] as a selection criteria on a date
field. When I enter a date range (04/04/05-04/30/05) I get all records for
April 2005 and April 2004

Might your table field be a Text field rather than a Date/Time field?
A criterion of

BETWEEN [Date1] AND [Date2]

on a date/time field should search chronologically. You might need to
open the query in design view; right mouseclick the background behind
the tables; select Parameters; and specify that [Date1] and [Date2]
are of Date/Time type.

If your table field is Text, you'll get the results you see, since the
text string "04/30/1328" sorts after the text string "04/29/2005"; 3
is larger than 2.

John W. Vinson[MVP]