Querying for Date() in Field Formatted General Date/Time

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

Guest

I've either had too much Mountain Dew or not enough, because I cannot get
what should be a simple query to return desired results.

My only criteria is trying to find records with the current date using
Date(). The field is formatted as General Date because it stores date and
time.

Why isn't this working? Any suggestions greatly appreciated!
 
Anita,

It isn't working because Date returns 12/07/2005 right now (in Greece),
whereas my timestamp an hour ago was 12/07/2005 15:51:39. Obviously, the
two are not equal! Add a calculated field in your query, applying the
DateValue() function on your date field, so you strip off the time part,
then apply the Date() criterion on that field.

This happens because Access (and Excel, and anything that's MS, I guess)
stores date and time as a number. Date 0 is Dec.31, 1899, evbery 24-hour
day after that is +1, time is a fraction of 1; today 12 noon was 38,545.50

HTH,
Nikos
 
If your Table Field stores both date & time values, you cannot use the exact
match equality (=) since the Date will return date value (with zero time
component) and your Field values will in general have non-zero time and the
equality comparison will return False most of the times. Hence most, if not
all, Records won't be selected.

Try setting your criteria to:

( [YourField] >= Date() ) AND
( [YourField] < DateAdd( "d", 1, Date() ) )
 
Back
Top