Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Summing Time

Status
Not open for further replies.

RangerFan

MIS
May 4, 2000
61
US
My last question for today. Everybody here is so helpful.

I need to sum a column of times on a report and display a grand total on the report footer. Sum hasn't worked for me so far. The times print as follows on the report:

1:00
0:30
0:15
1:30

I need to total these.

As always, any help is appreciated.
 



Keep in mind that there's a fundamental difference between TIME (think of it as Time of Day) and DURATION.

You cannot add Times. What does it mean to add 6:00 AM plus 11:59 PM?

But you can add Durations or Time. and you can add Durations of Time to a Time Valuv or Time of Day.

Time is store as DAYS, actually FRACTIONS of a day.

Duration should not be stored as Time (time of day) but as Hours or minutes or Seconds. THEN, you can do math on them to your heart's delight.

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
My problem is that all I have to work with is the data in the column as is. Is there a way to convert the "1.30" to hhmm?
 



Well it is possible to add the values in the table.
Code:
Select SUM([My Time Field])
From [My Table]
ought to do it.

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Suggest you convert to the hours ie before the :, to minutes. Multiply by 60 and add the minutes after the :. Ths will give your durations in mins which you then need to total again and convert back to HH:mm

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.
 



Ian,

To convert a TIME VALUE to minutes, all you need do is multiple the TIME VALUE by 24, as TIME VALUE units is Days and there are 24 hours in a day.

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Thank you everyone for your help. For the moment, the solution to adding these numbers in an Access report has eluded me, however, I was able to get the results I needed in Excel. An agreeable work around for the moment.

Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top