Results 1 to 8 of 8
  1. #1
    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)
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    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. #4
    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. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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))
    Have a nice day!

  6. #6
    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.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    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/microsoft-ac...es-report.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •