Between Clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I have the following formula in a query in order to retrieve records that
fall between two dates:

Between [Start Date] And [End Date]

However, when I run the query it doesn't include records for the end date
that I have keyed in. I tried typing in "... [End Date] + 1", but I received
an error. Is there anything else I can do to include records that include
the End Date?

Thanks in advance!
 
Sherwood said:
Greetings,

I have the following formula in a query in order to retrieve records
that fall between two dates:

Between [Start Date] And [End Date]

However, when I run the query it doesn't include records for the end
date that I have keyed in. I tried typing in "... [End Date] + 1",
but I received an error. Is there anything else I can do to include
records that include the End Date?

Thanks in advance!
Click to expand...

Your criteria (if you enter only dates) is effectively looking for records
between midnight on the Start Date and midnight on the End Date which is why
records past midnight on the End Date are not included.

Adding one to the End Date is a common way to deal with this problem and it
should work. What was the error you recieved? You could try...

BETWEEN [Start Date] and DateAdd("d", 1, [End Date])
 
Greetings,

I have the following formula in a query in order to retrieve records that
fall between two dates:

Between [Start Date] And [End Date]

However, when I run the query it doesn't include records for the end date
that I have keyed in. I tried typing in "... [End Date] + 1", but I received
an error. Is there anything else I can do to include records that include
the End Date?

Thanks in advance!
Click to expand...

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then Change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = int([DateField]);

Then to make sure the time is not included in any new entries use
Date() instead of Now().
 
Back
Top