Dcount past time

[StuJol]

i have a table, using a medium date just to give me the time function

1 02:00 AM
2 02:30 AM
3 03:30 AM

current time is 03:00, is using the following code
DCount("*","[Reminders]","[Time]<Format(Now(),"hh:mm AMPM")")
which should return a count of 2 but im getting compile error.

Can anyone tell why please?

 

[BruceS]

The simple answer is that you're comaring a string from the format function
to a numeric value stored in the table. (Using the format function creates a
string.) You have to tell Access to interpret the string as a date/time
value:

DCount("*","[Reminders]","[Time] < #" & Now() & "#")

Your problem may run deeper, however. If you used the Date/Time field type
in the "Reminders" table, then Access is storing the entire date. The format
command only makes it show you only the time. If that's the case, you may
not get the counts you expect.

If you removed the date part before storing the value in the table, then the
"where" in the function above needs to be changed to:

"[Time] < #" & Now() - Date() & "#"

The "Now() - Date()" returns only the "decimal" part of a date value - the
time.

Unless the value in the table is stored as a string, there is no need to use
the format function at all.

Bruce

 

[John Spencer]

DCount("*","Reminders","[Time]<Time())"

Using Time as field name is a bad idea, since Time is a VBA function
that returns the current system time. You would do better to change the
field name to ReminderTime or ActionTime or some other title that
reflects what the time is or does.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================