Between Dates

[Lamar]

Use the following syntax in a query: Between #StartDate# And #EndDDate#
User input the StartDate and EndDate on a form.

Problem: For example if the user enters the StartDate as 12/01/06 then
sometimes the query does retrieve the all records dated 12/01/06. I do not
know why this happening? I want the query to retrieve all records dated for
the StartDate, EndDate and all the records between.

Is there a way change the syntax to ensure all the records will be retreived?

Thanks for any help.

 

[Michel Walsh]

Well, maybe the date format is not always mm/dd/yyyy ? Also, if you stored
a time, in addition to the date, in the field, note that today at 06:00:00
is later than today (at 00:00:00), so, if you say

dateField BETWEEN x AND Today()

all records of today, but later than 00:00:00 probably fall outside the
range. Preferable, in that case to say

dateField >=x AND dateField < Today() + 1


where +1 effectively stands for + one day. Using < would then stand for up
to, including, today at 23:59:59



Hoping it may help,
Vanderghast, Access MVP