Total Hours per week

drewdt3 · Sep 18, 2006

I have a really annoyong problem in a report that when I try to calculate the total hours per person for the week the sum comes up with time that are well short of the mark. My total box in design view reads - =Sum([total hours])
Can anyone suggest a modification to this line please. My sanity is in serious troble

statsman · Sep 18, 2006

Is [totalhours] the name of the field you are trying to total?

stopher · Sep 18, 2006

drewdt3 said:
I have a really annoyong problem in a report that when I try to calculate the total hours per person for the week the sum comes up with time that are well short of the mark. My total box in design view reads - =Sum([total hours])
Can anyone suggest a modification to this line please. My sanity is in serious troble

I suspect the problem is you are trying to sum a date string. When you sum up hour and the total comes to say 25.5 hours, this is represented as 1 day and 1.5 hours. You are seeing the 1.5 hours bit.

There's no easy way round this afaik. However, you can build a string prepresentation of the total using this instead of your sum expression:

Code:

=CStr(CInt(Sum(CDbl([total hours])*24))) & ":" & CStr(CInt((Sum(CDbl([total hours])*24)-CInt(Sum(CDbl([total hours])*24)))*60))

Edit: The above code is incorrect, see correct code below

This should work on it's own. However, you can simplify this by incorporating a SUM(CDbl([total hours])*24) into your source query and then replacing the instances of this snippet in the above expression with the query field name.

hth
Stopher

drewdt3 · Sep 18, 2006

Thanks

Stopher, thanks for that, god only knows how you came up with it. One strange thing about it though is that it is ok when the total comes to an exact hour, however when it is say 37hours and 30mins it comes out as
38:-30, is thier a way around this?

stopher · Sep 18, 2006

drewdt3 said:
Stopher, thanks for that, god only knows how you came up with it. One strange thing about it though is that it is ok when the total comes to an exact hour, however when it is say 37hours and 30mins it comes out as
38:-30, is thier a way around this?

Oops

. I overlooked the fact that Cint rounds up/down whereas Int only rounds down. Try this:

=CStr(Int(Sum(CDbl([total hours])*24))) & ":" & CStr(Int((Sum(CDbl([total hours])*24)-Int(Sum(CDbl([total hours])*24)))*60))

Do sense check it until your confident it does the business.

Stopher

drewdt3 · Sep 19, 2006

Thanks

Stopher

What can I say but thanks very much for your time and genius. I don't understand a bit of it but it works!!

Cheers

Drew

Total Hours per week

drewdt3

Registered User.

statsman

Active member

stopher

AWF VIP

drewdt3

Registered User.

stopher

AWF VIP

drewdt3

Registered User.

Similar threads

Users who are viewing this thread