Results 1 to 6 of 6

20010821, 22:32 #1
 Join Date
 Jan 2001
 Location
 Houston, Texas, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
How to I add up hours/min (Access 97)
I have a db where I am entering total time run for each record. When I display the records it shows the correct totals.... 2:32,,19:13. THis field is called expr2.
I have a report were I am trying to grab the records by date and add up the totoal number of hours for each date. The hours displayed for each record are correct by when I try to add them up the totals are wrong. I might have a total of 54 hrs 23min.(manual add) and the report show 5hr22min. I use the formula =sum([expr2]) to add them up.
with this information can anyone show me how to add hours and min. from a large numbe of records. I think it has to do with the number of hours in a day (24).

20010822, 01:19 #2
 Join Date
 Dec 2000
 Location
 USA
 Posts
 379
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to I add up hours/min (Access 97)
I have done a similar task and it works correctly to add employee time for payroll. Since you haven't gotten an reply yet, I'll offer mine, but am not at all certain it is efficient. Here is the method. First I use DateDiff to get the total time in minutes at the record level. When I want the total for all the records, I use Sum([Minutes] and call it TotalMinutes. Then I use Int([TotalMinutes]/60) to get the hours. Then I use [TotalMinutes] Mod 60 to get the remaining minutes.
If there is a better way, I'd also like to know what it is. ( By the way, given the 24 hours in a day, I don't understand why your manual sum and calculated sum come up with those numbers.)

20010822, 03:02 #3DaveShmaveGuest
Re: How to I add up hours/min (Access 97)
Tom 
"( By the way, given the 24 hours in a day, I don't understand why your manual sum and calculated sum come up with those numbers.)"
Because when the clock gets to 24 (2400) it rolls back over and starts counting all over again (from 0100). Therefore, you really can't calculate time in the manner that Dan's trying to do it.
Dan 
I've had this problem and have seen solutions (or at least work arounds) before and when I locate it I'll come back and post what I find. Although Tom's solution does the job. For another, slightly different, approach:
Change your time field to minutes (:1440 total in a day ~ 24 hours in a day * 60 minutes per hour) and divide your total minutes by 60. You're answer will be in hours. So 17.35 hours = 17 hours and 21 minutes.
HTH
Dave

20010822, 12:34 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to I add up hours/min (Access 97)
Your problem is that when you add time you get days and hours and you are only dislpaying hours. 54 hours and 23 minutes is two days, 5 hours and 23 minutes. When you display that as a time value you get just 5 hours and 23 minutes. I'm not an Access programmer, so I don't know the proper method of handeling that in Access. In Excel you would use a format of [h]:mm with the brakets around the h. That tells Excel to display the time value as an elapsed time and it would display 54:23. I don't know what the equivalent would be in Access.
Legare Coleman

20010822, 14:03 #5
 Join Date
 Dec 2000
 Location
 Columbus, Ohio, USA
 Posts
 286
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to I add up hours/min (Access 97)
Dan: I use this calculation to sum times:
In the Control Source property:
=Int(Sum([timefield]))*24 + DatePart("h",Sum([timefield])) & ":" & DatePart("n",Sum([timefield]))

20010822, 15:41 #6
 Join Date
 Jan 2001
 Location
 Houston, Texas, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How to I add up hours/min (Access 97)
This last one did it. Worked great. Now need to make sure that I keep this and don't louse it.
Thank you