Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Unanswered: How Do I add hours to a sum?

    I have two colums having start and end hours, and a third row automatically getting the amount of hours between, for example:

    08:00 - 13:00 -> 05:00 (hours)
    14:00 - 22:30 -> 08:30 (hours)

    etc etc.

    In the report, I display everything and it all works perfectly.

    Now, In the bottom of the report, below the hours, I would like to have a sum of all the hours. I tried the quickie following:

    =Sum(Hour([total_daily]))

    And it always sends back an error.

    What is the right way to do this?


    Thank you all!

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    I assume you can calculated the time difference in a query and you've called the field total_daily.

    You can then add a text box to the section footer or report footer with the control source:
    =sum([total_daily])

    Format the field as ttttt

    Note that if you try to sum in the page footer then you will probably get an error.

    Chris

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    when i'm doing that, the results makes no sense

    attached is a picture of my report, you can see all the total_daily rows
    and below rounded with red, you see the result of the sum.

    how can that be? :\

    Thank you all
    Attached Thumbnails Attached Thumbnails Untitled-1 copy.gif  

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi again

    Time is a 24hour clock. So add up 16 hours and 9 hours and the time result will be 01:00 (25:00 o'clock!). Hence your 20:30 is correct in clock terms. So what I suggested only works up to 24hours - sorry

    You will need to convert the result of the sum function to something that looks like hh:mm. This is easy in Excel [h]:mm. However, I don't know how to do this in Access easily. However, you can write a formula to do the trick:

    =CStr(Int(Sum([total_daily])*24)) & ":" & CStr((Sum([total_daily])*24-Int(Sum([total_daily])*24))*60)

    Since the results of the sum is a decimal number of days e.g. 1.5 is 1.5 days, then the above works out the equivalent in hours and minutes and uses some string functions to make it look nice. You could create a function for this if you are planning to use it in lots of reports/forms etc.

    It would be interesting to know if anyone knows an easier way of doing this.

    Chris

  5. #5
    Join Date
    Dec 2004
    Posts
    41
    That worked, i'll improve that along the way, Thank you very much man.

Posting Permissions

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