Link to home
Start Free TrialLog in
Avatar of Zaurb
Zaurb

asked on

MS Access: SUM on Date/Time fields

Hi!

I have a table as follows:
tblCalls with fields
UserId                  Number
PhoneNumber      Text
CallTime               Date/Time
CallDuration         Date/Time

Call duration is like this:
12:15:26
14:09:15
01:40:30
etc

When I do SUM on CallDuration I get 04.05.11 instead of let's say 28:05:11.

Is there a workaround for this?

Thanks!

SELECT Format(Sum(tblCalls.CallDuration),"Long Time") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));

Open in new window

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

SELECT Int(CallDuration * 24) & ":" & Format((CallDuration * 1440) Mod 60, "00") & ":" & Format((CallDuration * 86400) Mod 3600, "00") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));
It's not possible to express times over 23:59:59 in an Acces datetime field.
Hour values in excess 24 hours and more get converted into days.

To display the result as if it were hours:mins:secs you have to build the result by calculating the hours, minutes and seconds and then concatenating them into a string.

If you have a datetime variable total called Mytotal then you can do:

cstr(datevalue(mytotal)*24+hour(mytotal)) & ":" & format(mytotal,"nn:ss")  
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gustav,

Thanks, got a little sloppy there :)

Regards,

Patrick