Between Date Rand Date/Time field

  • Thread starter Thread starter Tina
  • Start date Start date
T

Tina

Is there a simple criteria command that would allow a user
to enter the same begin and read date and pull the data
from a date/time field? ie: 10/16/03-10/16/03. Right now
to get the data in the date/time field you have to enter
10/16/03-10/17/03 to allow for the times...
 
Is there a simple criteria command that would allow a user
to enter the same begin and read date and pull the data
from a date/time field? ie: 10/16/03-10/16/03. Right now
to get the data in the date/time field you have to enter
10/16/03-10/17/03 to allow for the times...
Click to expand...

A Date/Time is stored internally as a Double Float number, a count of
days and fractions of a day (times) since midnight, December 30, 1899.
For instance 10/16/03 is equivalent to midnight on that date, and
stored as 37910.0000000000000. Noon on that date was 37910.5, which
is NOT between 37910 and 37910!

To pick up date/times on the last day (or, as in this case, only day)
of a range, and to ensure that dates are interpreted correctly, I
usually use a criterion of
= DateValue([Enter start date:] AND < DateAdd("d", 1, DateValue([Enter end date:]))
Click to expand...

DateValue will trim off any user entered time portion - use CDate
instead if you want to be able to find records entered between
10/16/03 11:30am and 10/17/03 2:31pm.
 
Beautiful...worked perfectly...I just had to alter the parens a bit to: >= DateValue([Enter start date:]) AND < DateAdd("d", 1, DateValue([Enter end date:]))
 
Back
Top