Caculating the Difference in Time in a Query

[pushrodengine via AccessMonster.com]

I have a query “qryUrbanâ€. Within this query are the fields “10-97Time and
“10-8Timeâ€. The fields represent military time. I’m trying to find the
difference of the two times, but as you can imagine I have ran into a problem.


The problem being: If the “10-8Time†is 1359 and the “10-97Time†is 1403.
When calculating the difference normally the answer would be 44, but I need
the expression to recognize the values are in military time and produce the
correct answer.

Is this even possible?

If so how?

Thank You

 

[Allen Browne]

You can convert the time to a number of minutes like this:
(60 * ([10-97Time] \ 100)) + ([10-97Time] Mod 100)

Do that for the other field as well, and can then calculate the difference
in minutes between the 2 times.

In general, having the number of minutes is the best approach, but if you
want to display the result as a string where it shows as hours and minutes,
use the approach explained here:
http://allenbrowne.com/casu-13.html

If you prefer, you could convert the fields into real time values like this:
CDate(Format([10-97Time], "00\:00"))

 

[pushrodengine via AccessMonster.com]

Thank you for your help Allen Browne!