sum issue

  • Thread starter Thread starter Pete Davis
  • Start date Start date
P

Pete Davis

I don't think there's any way to sum time spans like that.

What you might want to do, if it's possible, is store the time span in
minutes, sum the minutes, and then convert to hours:minutes

Pete
 
Hi,

On a report that I have I would like to sum a list of
records that are formatted/input-mask as HH:MM (as in,
01:30 + 00:45 = 02:15)

How can I go about doing this?

Thanks!
 
On a report that I have I would like to sum a list of
records that are formatted/input-mask as HH:MM (as in,
01:30 + 00:45 = 02:15)
Click to expand...


In Access (and other databases) the actual value stored in the
DateTime field is a decimal based on the number of days.
This is then formatted to the Date/Time values we want to see.

So you could just put in Sum(MyTimeField) but the result would
show the number of days e.g. 1.5 would equal 1 day 12 hours. or 36:00

You really need a custom function to put this back to hours and minutes.

Something like: -

Function HoursAndMins(dblTime As Double) As String
Dim intHours As Integer
Dim intMinutes As Integer

intHours = Int(dblTime * 24)
intMinutes = Int((((dblTime * 24) - intHours) * 60) + 0.5)
HoursAndMins = intHours & ":" & intMinutes

End Function

In the control box on the form this would then be

=HoursAndMins(Sum([MyTimeField]))

Please don't rely on the function above it needs checking out!

Cheers,
Peter
 
Minutes = DateDiff("n",#10:34#,#11:50#)
The problem is that you need to either use AM/PM or use 24 hour time. Also,
when the day goes over midnight, it is better to include the date too.
Minutes = DateDiff("n",#04/04/2004 10:34 AM#, #04/05/2004 1:35 PM#)
I tested both and both work fine. You could replace variables formatted as
date instead of the hard-code examples.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
 
Back
Top