Results 1 to 8 of 8

022412, 13:02 #1Registered User
 Join Date
 Feb 2012
 Posts
 24
Unanswered: Adding Hours Formats Greater Than 24 Hours
Hi,
In my database each record has 14 fields where total time spent on a client visit is stored. The fields are Date/Time, Short Time format, so hours are inputted like "03:30:00" for 3 hours 30 minutes.
I then have a report which need to total the hours in these 14 fields per record, and then total these totals for an overall total.
First, I created a text box "IndHrs", and inputted the following formula:
Code:=Nz([1) Total Hours:],0)+Nz([2) Total Hours:],0)+Nz([3) Total Hours:],0)+Nz([4) Total Hours:],0)+Nz([5) Total Hours:],0)+Nz([6) Total Hours:],0)+Nz([7) Total Hours:],0)+Nz([8) Total Hours:],0)+Nz([9) Total Hours:],0)+Nz([10) Total Hours:],0)+Nz([11) Total Hours:],0)+Nz([12) Total Hours:],0)+Nz([13) Total Hours:],0)+Nz([14) Total Hours:],0)
Code:=[IndHrs]*24
My totals can range from 1 hour to 50 hours, so the formula needs to work for all variances.
Is there a way of working with the Iif expression to get it to only multiply by 24 if its between a certain range?

022512, 08:48 #2Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14Use a function to perform the addition and format the result:
Code:Public Function SumTimes(ParamArray Times() As Variant) As Variant Dim i As Long Dim lngHours As Long Dim lngMins As Long For i = 0 To UBound(Times) lngHours = lngHours + DatePart("h", Times(i)) lngMins = lngMins + DatePart("n", Times(i)) Next i If lngMins > 59 Then lngHours = lngHours + lngMins \ 60 lngMins = lngMins Mod 60 End If SumTimes = CStr(lngHours) & ":" & Format(lngMins, "00") End Function
Have a nice day!

022712, 05:50 #3Registered User
 Join Date
 Feb 2012
 Posts
 24
Thank you for that, but I am unsure what to do with it?
Where do I input that, and how do I reference it to the fields I wish to add up?

022712, 10:11 #4Registered User
 Join Date
 Feb 2012
 Posts
 24
Overall Total of Hours & Minutes in Report
Hi,
I recently posted a thread asking for help adding totals up for hours and minutes (http://www.dbforums.com/microsoftac...ml#post6540209). I have since then moved on with help from Google but am stuck with totalling a report.
What I Have Now:
In The Database
14 rows per record where staff can input hours and minutes worked into seperate fields per day. I.e. for Day 1, there is a field named "1) hrs" and a field named "1) min" field. For Day 2, there are fields named "2) hrs" and "2) min", and so on up to day 14. Note, these fields are formatted for Number: General Number.
In My Report
A nonvisible field (named "IndHrs") which adds up the hours from the 14 "hrs" fields into a total using the code:
Code:=Nz([1) Hrs:],0)+Nz([2) Hrs:],0)+Nz([3) Hrs:],0)+Nz([4) Hrs:],0)+Nz([5) Hrs:],0)+Nz([6) Hrs:],0)+Nz([7) Hrs:],0)+Nz([8) Hrs:],0)+Nz([9) Hrs:],0)+Nz([10) Hrs:],0)+Nz([11) Hrs:],0)+Nz([12) Hrs:],0)+Nz([13) Hrs:],0)+Nz([14) Hrs:],0)
Code:=Nz([1) Min:],0)+Nz([2) Min:],0)+Nz([3) Min:],0)+Nz([4) Min:],0)+Nz([5) Min:],0)+Nz([6) Min:],0)+Nz([7) Min:],0)+Nz([8) Min:],0)+Nz([9) Min:],0)+Nz([10) Min:],0)+Nz([11) Min:],0)+Nz([12) Min:],0)+Nz([13) Min:],0)+Nz([14) Min:],0)
Code:=IIf(([IndHrs]+IndMin.Value\60)<10,"0"+CStr([IndHrs]+IndMin.Value\60),CStr([IndHrs]+IndMin.Value\60))+":"+IIf((IndMin.Value Mod 60)<10,"0"+FormatNumber(IndMin.Value Mod 60,0),FormatNumber(IndMin.Value Mod 60,0))
Code (like the above) that I can enter into the Control Source of a text box in the Report Footer that will 1) add up the "COMTOT" field for each record OR add up the IndHrs and IndMin fields (if this is easier) and 2) Format them into hh:mm much like the COMTOT field does currently.
If somebody could help, I would be extremely grateful because I have been working on this for days. I am going in blind, because I have never used Access before, and the codes above were written by other people online and I have had to work to modify it for my database.

022712, 11:31 #5Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14You place it in an independent module and it can be used as a builtin function, in a query or in a VBA procedure:
Code:Me.IndHrs.Value = SumTimes(Nz([1) Total Hours:],0), Nz([2) Total Hours:],0), Nz([3) Total Hours:],0), Nz([4) Total Hours:],0), Nz([5) Total Hours:],0), Nz([6) Total Hours:],0), Nz([7) Total Hours:],0), Nz([8) Total Hours:],0), Nz([9) Total Hours:],0), Nz([10) Total Hours:],0), Nz([11) Total Hours:],0), Nz([12) Total Hours:],0), Nz([13) Total Hours:],0), Nz([14) Total Hours:],0))
Have a nice day!

022712, 12:49 #6Registered User
 Join Date
 Feb 2012
 Posts
 24
After many, many, many attempts, I have got this to work. I will post my solution in case anybody else is suffering with the same problem.
What I had to do was create three text boxes in the report footer.
The first one (made nonvisible) I called 'TotHrs', and used the following code to get it to add the 'IndHrs' fields (see post above). This gave me my total hours.
Code:=Sum(Nz([1) Hrs:],0)+Nz([2) Hrs:],0)+Nz([3) Hrs:],0)+Nz([4) Hrs:],0)+Nz([5) Hrs:],0)+Nz([6) Hrs:],0)+Nz([7) Hrs:],0)+Nz([8) Hrs:],0)+Nz([9) Hrs:],0)+Nz([10) Hrs:],0)+Nz([11) Hrs:],0)+Nz([12) Hrs:],0)+Nz([13) Hrs:],0)+Nz([14) Hrs:],0))
The second one (also nonvisible) I called 'TotMins', and this follows the example above:
Code:=Sum(Nz([1) Min:],0)+Nz([2) Min:],0)+Nz([3) Min:],0)+Nz([4) Min:],0)+Nz([5) Min:],0)+Nz([6) Min:],0)+Nz([7) Min:],0)+Nz([8) Min:],0)+Nz([9) Min:],0)+Nz([10) Min:],0)+Nz([11) Min:],0)+Nz([12) Min:],0)+Nz([13) Min:],0)+Nz([14) Min:],0))
Code:=IIf(([TotHrs]+[TotMin].[Value]\60)<10,"0"+CStr([TotHrs]+[TotMin].[Value]\60),CStr([TotHrs]+[TotMin].[Value]\60))+":"+IIf(([TotMin].[Value] Mod 60)<10,"0"+FormatNumber([TotMin].[Value] Mod 60,0),FormatNumber([TotMin].[Value] Mod 60,0))
To make things simple, here is a quick start guide:
How to Total Hours and Minutes in Access 2010
This guide works of the assumption that you have multiple fields per record for inputting hours and a seperate field for inputting minutes. These fields must be set to 'Number' > 'General Number'.
In the 'Detail' section of the report, you need to create three fields formatted for Number > General Number: (Note: The detail section is where it will show details for each individual record in the report.)
Field 1: In this example, this is called "IndHrs". Go to the properties of the field and set it to 'visible: no', so it won't show up on the report. Insert the following code:
Code:=Nz([1STHOURSFIELDNAME],0)+Nz([2NDHOURSFIELDNAME],0)
Field 2: In this example, this is called "IndMin". It is much like the one above. Go to the properties of the field and set it to 'visible: no', so it won't show up on the report. Insert the following code:
Code:=Nz([1STMINUTESFIELDNAME],0)+Nz([2NDMINUTESFIELDNAME],0)
Field 3: Make sure this one is visible. The following code will add up the minutes, convert them to hours and add this to the total hours. It will then place the remaining minutes to the right of the ":" and format it in HH:MM format:
Code:=IIf(([IndHrs]+IndMin.Value\60)<10,"0"+CStr([IndHrs]+IndMin.Value\60),CStr([IndHrs]+IndMin.Value\60))+":"+IIf((IndMin.Value Mod 60)<10,"0"+FormatNumber(IndMin.Value Mod 60,0),FormatNumber(IndMin.Value Mod 60,0))
Field 1: In this example, it is called "TotHrs". Gives total hours for the whole report:
Code:=SUM(Nz([1STHOURSFIELDNAME],0)+Nz([2NDHOURSFIELDNAME],0))
Code:=SUM(Nz([1STMINUTESFIELDNAME],0)+Nz([2NDMINUTESFIELDNAME],0))
Code:=IIf(([TotHrs]+[TotMin].[Value]\60)<10,"0"+CStr([TotHrs]+[TotMin].[Value]\60),CStr([TotHrs]+[TotMin].[Value]\60))+":"+IIf(([TotMin].[Value] Mod 60)<10,"0"+FormatNumber([TotMin].[Value] Mod 60,0),FormatNumber([TotMin].[Value] Mod 60,0))

022712, 12:52 #7Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59I'd add up the elapsed time
then format the resultant value
if elaspedtime>1 then
format(elapsedtime, "dd days, ") & format(elapsedtime1,"HH:MM")
else
format(elapsedtime,"HH:MM")
endifI'd rather be riding on the Tiger 800 or the Norton

022712, 12:55 #8Registered User
 Join Date
 Feb 2012
 Posts
 24
Thank you for your help.
It turns out it was easier to store the hours and minutes in seperate boxes as number formats, rather than as HH:MM. This bypasses the need to worry about it going over 24 hours.
I explain how I got it working here: http://www.dbforums.com/microsoftac...esreport.html