Caculating the Difference in Time in a Query

  • Thread starter Thread starter pushrodengine via AccessMonster.com
  • Start date Start date
P

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
 
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"))
 
Back
Top