Between Dates

M

Mark

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?
 
R

Rick Brandt

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.
 
V

Van T. Dinh

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]))
 
V

Van T. Dinh

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.
 
V

Van T. Dinh

"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.
 
Top