1. Registered User
Join Date
Feb 2012
Posts
24

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)`
This works fine until the time goes over 24 hours. After some research, I was told to make the "IndHrs" box not visible, and create a new one with the following formula:

Code:
`=[IndHrs]*24`
My problem is this only works if the value equals greater than 23:00 but less than 47:59.

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?

2. Moderator
Join Date
Mar 2009
Posts
5,442
Use 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```

3. Registered 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?

4. Registered 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/microsoft-ac...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 non-visible 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)`
A non-visible field (named "IndMin")which adds up the minutes from the 14 "min" fields into a total using the code:

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)`
A visible field (named "COMTOT"), which adds up the totals from the two fields above (IndHrs and IndMin), converts the minutes to hours and formats the results into 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))`
What I Need
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.

5. Moderator
Join Date
Mar 2009
Posts
5,442
You place it in an independent module and it can be used as a built-in 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))`

6. Registered 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 non-visible) 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))`
To make it easier, this is the exact code from the 'IndHrs' field, except with "=sum" at the start.

The second one (also non-visible) 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))`
So now I had my total hours and total minutes. It was now a simple case of copying the code from the COMTOT field (again, see above), and inserting 'sum' plus the relevent brackets in order for it to convert minutes to hours, add this to the hours and format in a usable format:

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))`
I hope this is helpful to someone else, because if I had seen a thread like this it could have saved me hours and hours of trial and (much) error.

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)`
The NZ and the 0 section just tells it put a '0' if the field is empty. If you have more than two hours fields to add up, just repeat the code for each hours field.

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)`
You now have two boxes, one totalling the hours and one totalling the minutes.

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))`
Next, if you want to total these for all records, you need to create 3 fields in the 'Report Footer'. The first two fields again need to be marked as not visible. The codes are very similar to the ones above, but with added 'SUM' functions to add them up:

Field 1: In this example, it is called "TotHrs". Gives total hours for the whole report:
Code:
`=SUM(Nz([1STHOURSFIELDNAME],0)+Nz([2NDHOURSFIELDNAME],0))`
Field 2: In this example, it is called "TotMin". Gives total minutes for the whole report:
Code:
`=SUM(Nz([1STMINUTESFIELDNAME],0)+Nz([2NDMINUTESFIELDNAME],0))`
Field 3: Totals the hours, converts minutes to hours and formats the result:
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))`
Remember, field names must match your own fields.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
I'd add up the elapsed time
then format the resultant value
if elaspedtime>1 then
format(elapsedtime, "dd days, ") & format(elapsedtime-1,"HH:MM")
else
format(elapsedtime,"HH:MM")
endif

8. Registered User
Join Date
Feb 2012
Posts
24