Total Hour Duration Query

[Stephen Young]

Hello

Have a bit of a problem, im trying to get the total number of
hours:minutes for all actions over the month in a query, have tried
two methods to no luck...
Duration This Month:
Format(Sum([tblIssues].[timeOff]-[tblIssues].[timeON]),"hh:nn")
displays the total hours but rolls over anything over 1 day and starts
over from 0 again

Duration This Month: CDate(Sum(tblIssues.timeOff-tblIssues.timeON))
displays it as the following if over 24 hours, ie for 32 hours would
display: 31/12/1899 08:00:00

Is there a way of totalling up the hours and displaying it in a
readable way within a query? The timeOn and timeOff are time fields
only as nothing would roll over 24 hours per instance although the
total in the month would exceed this.

Thanks for any advice.

Stephen

[Arno R]

Stephen,

First: you will have to be sure that timeOff is in the same day as timeON.
In your query you simply set as criterion: timeOff >= timeOn
Second: Try a third calculation-method.
Calculate the timedifference in *minutes*, add these minutes and then recalculate the
hours.

Check out the DateDiff function in the Help for this.
Minutes=Datediff("n",[timeON],[timeOff])
e.g. Sum in Minutes is 2356 then your number of hours is 2356 \60 and your Minutes left
will be 2356 mod 60.
This will be 39 hours and 16 minutes

--
Hope this helps
Arno R
"Stephen Young" <st***********@cwcom.co.uk> schreef in bericht
news:32**************************@posting.google.c om...

Hello

Have a bit of a problem, im trying to get the total number of
hours:minutes for all actions over the month in a query, have tried
two methods to no luck...
Duration This Month:
Format(Sum([tblIssues].[timeOff]-[tblIssues].[timeON]),"hh:nn")
displays the total hours but rolls over anything over 1 day and starts
over from 0 again

Duration This Month: CDate(Sum(tblIssues.timeOff-tblIssues.timeON))
displays it as the following if over 24 hours, ie for 32 hours would
display: 31/12/1899 08:00:00

Is there a way of totalling up the hours and displaying it in a
readable way within a query? The timeOn and timeOff are time fields
only as nothing would roll over 24 hours per instance although the
total in the month would exceed this.

Thanks for any advice.

Stephen

[Stephen]

Thanks for the reply, managed to get it working using what you
suggested and the final expression in the query is

Duration This Month:
Sum(DateDiff("h",[tblExceptions].[timeOn],[tblExceptions].[timeOff])) &
":" &
Sum(DateDiff("n",[tblExceptions].[timeOn],[tblExceptions].[timeOff])
Mod 60)

which shows the time and is roughly accurate however for a four minute
duration it would show 0:4 and im not able to sort by duration, is
there a way to show it neatly as HH:MM and let access recognise this as
a time but display hour totals greater than 23:59

[rquintal]

I handled this problem by creating a function that takes the difference
in seconds between two times and formats the result.
Your expression would become
Sec2Dur(Sum(DateDiff("s",[tblExceptions].[timeOn],[tblExceptions].[t*imeOff])))
Public Function Sec2Dur(seconds As Long) As String
'Converts seconds to hhhhh:mm:ss format
On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":" &
Format(secs, "00")

End Function
Good luck

[Arno R]

Stephen,

As I said: "Calculate the timedifference in *minutes*, add these minutes and then
recalculate the hours."

What you do is unfortunately NOT correct ...
When the Sum of the minutes you count this way exceeds 60 then what ?

*First* you need to calculate and sum the minutes. *After* you format to hours and
minutes.
I understand you want to add the timedifference of several records?
Call a field TimeDiffInMinutes. TimeDiffInMinutes:
DateDiff("n",[tblExceptions].[timeOn],[tblExceptions].[timeOff])
Make a GroupBy query and Sum this field.

For display you can use a second query based on the first where you can do:
Duration This Month: CDate(TimeDiffInMinutes\60 & ":" & TimeDiffInMinutes mod 60)
This way you can also sort on TimeDiffInMinutes, which is what you say you want.

--
Hope this helps
Arno R
"Stephen" <st***********@cwcom.co.uk> schreef in bericht
news:11*********************@f14g2000cwb.googlegro ups.com...

Thanks for the reply, managed to get it working using what you
suggested and the final expression in the query is

Duration This Month:
Sum(DateDiff("h",[tblExceptions].[timeOn],[tblExceptions].[timeOff])) &
":" &
Sum(DateDiff("n",[tblExceptions].[timeOn],[tblExceptions].[timeOff])
Mod 60)

which shows the time and is roughly accurate however for a four minute
duration it would show 0:4 and im not able to sort by duration, is
there a way to show it neatly as HH:MM and let access recognise this as
a time but display hour totals greater than 23:59