query by date range

[Guest]

I have a query that filters the records with BETWEEN [ENTER STARTING DATE]
AND [ENTER ENDING DATE] in the criteria line of the query under the field
[DATE].

I have used this before without any issues in other queries, but this one is
pulling information as follows:

If I enter 9/1/05 and 9/2/05 in the start and end prompts, then it is giving
me records from 9/1, and 9/10-9/19, but no records from 9/2/05.

Why would it give me records that have days of the month that start with "1"?

Please help.

 

[Allen Browne]

Sounds like Access is misinterpreting either the date or the parameters.

In query design view, choose Parameters on the Query menu.
Enter 2 lines into the dialog, like this:
[ENTER STARTING DATE] Date/Time
[ENTER ENDING DATE] Date/Time

Is the date field stored in a table, or is is a calculated field? If it is a
Date/Time type field in your table (not a Text type field), you should not
need to do any more. If it is a Text field, change it into a Date/Time field
so it is interpreted correctly. If it is a calculated field, wrap the
calculation in CVDate() as disussed here:
http://allenbrowne.com/ser-45.html

 

[Chaim]

Are you certain that the data type of the DATE field is DateTime? It sounds
like it's Text.

Also, DATE is a very bad name for a field in a table. It provides no insight
into the use/meaning of the field and it is also a reserved VBA word.

 

[Guest]

It sounds like your Date column might not in fact be a Date/Time data type as
the behaviour you are experiencing is that which would be expected if the
data were of text data type. If so you should change the column's data type
to Date/Time in the table definition. I'd also endorse the suggestions that
(a) you change the name from Date to something like TransactionDate to avoid
confusion with the built in Date function and (b) you declare the parameters
as DateTime.

Another point to bear in mind is that the BETWEEN….AND operator can be
unreliable where the date/time values might include a non-zero time-of-day
element (which might be there without you realising it; the use of the Now
function as the default value is the usual cause of this). A more reliable
method of defining a date range is:

WHERE TransactionDate >= [ENTER STARTING DATE] And TransactionDate < [ENTER
ENDING DATE] + 1