Help to retrieve data Between ... And ...

[Silvio]

I have a field which has a date and time stamped in. I use a query to
retrieve record based on date range. The criteria under the date/time field
is as follow: Between [Start Date] And [End Date].

The problem is that when I enter for example from 8/1/08 [Start Date] to
8/31/08 [End Date] I am only getting data from 8/1/08 to 8/30/08. How can I
have the data from 8/31/08 also included? Keep in mind that my original field
is date/time field.

Thank you,
Silvio

 

[Lord Kelvan]

are you sure there is actually data on the 31st

if so you coudl try defining the paramaters

open your query in design view and then click query in the menu bar
then click paramaters then in there type [Start date] and set the data
type to date/time and on an new line type [end Date] and set it to
date/time

 

[Silvio]

Lord, yes I have 3 record with that date. If I enter 9/1/08 as end date then
it shows data from 8/31/08 as well. Entering the paramater as you suggested
works fine but I intend to enter the sql statement directly in my repot. It
appears to me that the end date is excluded from the valid range.

 

[KARL DEWEY]

I expect that your 8/31/08 dates have time associated that is after midnight.

Between 8/1/08 [Start Date] and 8/31/08 [End Date] does not include 8/31-08
3:10 AM.

Some add a day to the end date but it is better to strip the time from your
datetime field like Int([DateTime]) or DateValue([DateTime])
--
KARL DEWEY
Build a little - Test a little

Lord, yes I have 3 record with that date. If I enter 9/1/08 as end date then
it shows data from 8/31/08 as well. Entering the paramater as you suggested
works fine but I intend to enter the sql statement directly in my repot. It
appears to me that the end date is excluded from the valid range.

are you sure there is actually data on the 31st

if so you coudl try defining the paramaters

open your query in design view and then click query in the menu bar
then click paramaters then in there type [Start date] and set the data
type to date/time and on an new line type [end Date] and set it to
date/time

 

[John W. Vinson]

I have a field which has a date and time stamped in. I use a query to
retrieve record based on date range. The criteria under the date/time field
is as follow: Between [Start Date] And [End Date].

The problem is that when I enter for example from 8/1/08 [Start Date] to
8/31/08 [End Date] I am only getting data from 8/1/08 to 8/30/08. How can I
have the data from 8/31/08 also included? Keep in mind that my original field
is date/time field.

Thank you,
Silvio

The problem is that a Date/Time value is actually a Double, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such, a
record with #8/31/08 11:30:15# is NOT between 8/1/08 and 8/31/08 - it's
eleven hours plus after the endpoint.

Try a criterion of

= CDate([Start Date]) AND < DateAdd("d", 1, CDate([End date]))

 

[Bob Barrows [MVP]]

Think about this: when you enter 8/31/08, what time is represented by that
string? Keep in mind that the string has to be converted to a date/time
value, which ALWAYS has a time component.

Are you seeing the problem yet? What time is represented by .0?

The answer is midnight ... 00:00:00.

Now you say you have three records for 8/31/08 ... I suspect the times
stored in those values are greater than midnight, are they not? so, they
will all be greater than the date/time value obtained by converting
"8/31/08" to date/time.

The solution?
One school of thought says to eschew BETWEEN in this situation, using

datetimefield >= #8/1/08# and datetimefield < #9/1/08#

instead. To make that work with parameters, assuming the user will enter
8/31 for the end date, add 1 day to the End Date parameter:
datetimefield >= [Start Date] and datetimefield < DateAdd("d",1,[End Date])

Another suggests that since none of the values in the table contain
midnight, then using

datetimefield BETWEEN [Start Date] AND DateAdd("d",1,[End Date])
is just as effective.

If some of the values in the table DO contain midnight, then add just a
little less to the end date
datetimefield BETWEEN [Start Date] AND DateAdd("s",86399,[End Date])


Take your pick.

Lord, yes I have 3 record with that date. If I enter 9/1/08 as end
date then it shows data from 8/31/08 as well. Entering the paramater
as you suggested works fine but I intend to enter the sql statement
directly in my repot. It appears to me that the end date is excluded
from the valid range.

are you sure there is actually data on the 31st

if so you coudl try defining the paramaters

open your query in design view and then click query in the menu bar
then click paramaters then in there type [Start date] and set the
data type to date/time and on an new line type [end Date] and set it
to date/time

 

[Silvio]

Thank you guys for the help. John your suggestion works perfectly. You are
correct, the time is what's creating the problem. I removed the time from a
couple fields and it worked fine. Your criteria however, allow me to retrieve
what I need without make any modification to the data type in the table.
Thanks again

I have a field which has a date and time stamped in. I use a query to
retrieve record based on date range. The criteria under the date/time field
is as follow: Between [Start Date] And [End Date].

The problem is that when I enter for example from 8/1/08 [Start Date] to
8/31/08 [End Date] I am only getting data from 8/1/08 to 8/30/08. How can I
have the data from 8/31/08 also included? Keep in mind that my original field
is date/time field.

Thank you,
Silvio

The problem is that a Date/Time value is actually a Double, a count of days
and fractions of a day (times) since midnight, December 30, 1899. As such, a
record with #8/31/08 11:30:15# is NOT between 8/1/08 and 8/31/08 - it's
eleven hours plus after the endpoint.

Try a criterion of

= CDate([Start Date]) AND < DateAdd("d", 1, CDate([End date]))

 

[John W. Vinson]

Thank you guys for the help. John your suggestion works perfectly. You are
correct, the time is what's creating the problem. I removed the time from a
couple fields and it worked fine. Your criteria however, allow me to retrieve
what I need without make any modification to the data type in the table.
Thanks again

It will also take advantage of any index on the date/time field.