Overall Total of Hours & Minutes in Report

[StevenCV]

Hi,



I recently posted a thread asking for help adding totals up for hours and minutes (https://www.accessforums.net/showthr...eport?p=106429). 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:

=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:

=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.

=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.

[StevenCV]

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.

=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:

=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:

=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:

=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:

=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:

=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:

=SUM(Nz([1STHOURSFIELDNAME],0)+Nz([2NDHOURSFIELDNAME],0))

Field 2: In this example, it is called "TotMin". Gives total minutes for the whole report:

=SUM(Nz([1STMINUTESFIELDNAME],0)+Nz([2NDMINUTESFIELDNAME],0))

Field 3: Totals the hours, converts minutes to hours and formats the result:

=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.