Date Range

[citloner]

I have made a basic query which is supposed to allow me to search for all
records in a table in a certain range, for example, May records, using the
syntax "Between [enter start date] And [enter end date]" under the date
field criteria. The query seems to work but always excludes a few records
which should otherwise be included. Orginally all the dates in the table
were in a different format, so I changed them all to the same format
(D/M/YY). I think the field in the table is a text field. Is this the
issue, and if so why is it pulling up some dates but not all? What is the
syntax for D/M/YY? I didn't see it in the pull down options in the table.

 

[Allen Browne]

If you use a Text field to store you dates in, Access will select the dates
based on the *text* values (i.e. character by character.) Based on a text
comparision, 2 is greater than 10 (because the first character -- a 2 -- is
greater than the first character of the 10.)

Use Date/Time fields instead.

If you are fearful that Access may not understand your dmy dates correctly,
see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html