1. Registered User
Join Date
Dec 2004
Posts
41

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. Registered User
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. Registered User
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

4. Registered User
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. Registered User
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
•