query times

[macroapa]

Hi,

I have a database which hold times in the format hh:mm:ss

I want to count up the number of times greater than a set time (ie
count all the instances after 10am).

My database (for testing purposes) has 2 records, one with 10:00:00
and one with 12:30:00

my sql is:

(-Sum(Format(Prep.TimeOfInterviewL1,"hh:mm:ss")>"10:00:00")) AS
TimeOfAppt_10am

but when I run it, it only finds the one record and not 2.

I would appreciate it if someone can point me in the errors I have
made.

Thanks.

 

[Stefan Hoffmann]

hi,

My database (for testing purposes) has 2 records, one with 10:00:00
and one with 12:30:00
(-Sum(Format(Prep.TimeOfInterviewL1,"hh:mm:ss")>"10:00:00")) AS
TimeOfAppt_10am
but when I run it, it only finds the one record and not 2.

This seems to be correct, as you are not testing for equality (>=).


mfG
--> stefan <--

 

[Allen Browne]

Stefan has mentioned using the >= operator, which is crucial to your
example, but there is another issue here too.

The time component in a Date/Time field is like a floating point number, and
may not match exactly. Therefore it might be safer to use: