time calculation problem 1

[h11]

Time problem I have a database that I use to track computer usage by age groups
What I need to do is track the total hours used for the month by groups. What I did was created a update query to that subtracts the “time in” with the “time out” and updates the total time field “Format([Time in]-1-[Time out],"Short Time&quot” This works great how ever when I run a report and try to capture the months total time usage. Anything over 24 hours come in as 9 hours instead of 33 hours total. I have not been able to get this to sum correctly. What I need is a total sums by groups and then an overall total sums of all the groups. =Sum([qry monthy usage]![total time]) short time format. Any help would be appreciated thanks.

 

[TonyJollans]

Hi hacker11,

First off, your sums are (probably) correct. What's causing your problem is the formatting. The totals are actually interpreted as dates AND times and the 'short time' format says to only show the time elements.

There are no format options to show times in excess of 24 hours (Date/Time values are not even considered to be elapsed time); you can't build a custom format which will do it directly and you can't even format them as a number of days and hours.

What you can do is manipulate the values when you understand how they are held. Date/Times are held as numbers of days, 33 hours being, for example 1.375 days; to get the whole number of hours you must take the integer part of (24 times the values); to get the minutes you can format the value directly. Combining the two gives one possible way of getting what you want ..

=Int(Sum([qry monthy usage]![total time])*24)&":"&Format(Sum([qry monthy usage]![total time]),"nn&quot

One downside of this is that the result is a string so cannot be fed into further calculations. Exactly how you proceed depends on your particular situation - you might want to use (invisible) interim fields.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[h11]

your formula work great thank