Date and Time calculation

[Guest]

Hello all,
I have this query named qry_Release_Open_FixTime that works well to provide
a field FixTime.

SELECT tbl_UAT_Tickets.*, DateDiff("n",[Create Time],[Upload_Date])\60 &
Format(DateDiff("n",[Create Time],[Upload_Date]) Mod 60,"\:00") AS FixTime
FROM tbl_UAT_Tickets;

I would like to create a select query that will select the records that are
less than 4:00 hours (as displayed in the FixTime field).

I have tried this with records less that 2 and less than 0.16666. also
tried with these formats '#2#' and '#0.1666#.

SELECT qry_Release_Open_FixTime.Severity, qry_Release_Open_FixTime.FixTime
FROM qry_Release_Open_FixTime
WHERE (((qry_Release_Open_FixTime.FixTime)<"4"));

What is the proper way to select records with less than 4:00 displayed in
the FixTime field?

THANK YOU VERY MUCH for your assistance.

 

[Allen Browne]

The Format() function create a text value.
If you try to compare this to a tim value such as #4:00:00#, you will either
get an error (data type mismatch) or wrong results. For example, the string
"14" starts with a 1 which is less than "2", so 14 is less than 2 in a
string comparison.

The simplest solution is to remove the Format() function, so the query
generates a result in minutes. You can then find the records that are less
than 240 minutes.

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html