Between Dates

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

Guest

Hello all,

I am using the between dates in a DCount. I noticed last night that the
data it gets is only the one that are between that date and not the ones that
fall on that day of the criteria. Is this the case? If so, what is the best
way to make it include it?
 
Mark said:
Hello all,

I am using the between dates in a DCount. I noticed last night that
the data it gets is only the one that are between that date and not
the ones that fall on that day of the criteria. Is this the case?
If so, what is the best way to make it include it?
Click to expand...

Likely the dates in the table also include times other than midnight. When you
use dates without times as criteria you are actually testing for a time of
midnight so any date with a time later in the day is outside the bounds of your
between statement. Just add a day to the end date you are using.
 
You may have non-zero component in your DateTime values and in this case,
the BETWEEN ... AND .... clause (still) works correctly but not what you
expected. For example:

#06/27/2005 11:00# is greater than #06/27/2005#

(the second value is intepreted to be midnight, i.e. zero-time component.)

To be accurate, you need to change your criteria to

([Field] >= [StartDate]) AND ([Field] < DateAdd("d", 1, [EndDate]))
 
The OP can also use DateValue() on a date/time value to return the
"pure" date, so
DateValue([MyDate]) Between DateValue ([StartDate]) And
DateValue([EndDate])
will work as expected - obviously you don't need to apply DateValue()
to constant dates or fields that are guaranteed not to have time
components.

You may have non-zero component in your DateTime values and in this case,
the BETWEEN ... AND .... clause (still) works correctly but not what you
expected. For example:

#06/27/2005 11:00# is greater than #06/27/2005#

(the second value is intepreted to be midnight, i.e. zero-time component.)

To be accurate, you need to change your criteria to

([Field] >= [StartDate]) AND ([Field] < DateAdd("d", 1, [EndDate]))
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
DateValue() will work but I don't recommend using it for this. The reason
is that the function DateValue() will need to be called once for each Record
in the Table so it will be very inefficient if you have a large number of
Records in the Table.
 
You're right as long as you are only applying DateAdd to a constant
criterion, though the inefficiency is only a serious problem for big
datasets. If the start or end date depends on a field value, you
pretty much have to use my approach, though you actually only have to
apply DateValue to the date being tested (not to the two ends).
BTW, the problem with adding exactly one day to the end date is that
if you have a test date of exactly midnight at the start of the day
after the last date you want, it will be included. You really need to
add 0.9999 days to avoid this.


DateValue() will work but I don't recommend using it for this. The reason
is that the function DateValue() will need to be called once for each Record
in the Table so it will be very inefficient if you have a large number of
Records in the Table.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
"BTW, the problem with adding exactly one day to the end date is that
if you have a test date of exactly midnight at the start of the day
after the last date you want, it will be included. You really need to
add 0.9999 days to avoid this."

No problem with adding exactly 1. If you look at my original reply, you
will see that I avoided the BETWEEN ... AND ... and <= since both would
include the end value. I actually used < which excludes the end value and
therefore accurate to 23:59:59.

The 0.9999 is interpreted as 23:59:51 so it is actually less accurate than
what I used.
 
Back
Top