• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1847
  • Last Modified:

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

0
Zaurb
Asked:
Zaurb
  • 3
2 Solutions
 
Patrick MatthewsCommented:
SELECT Int(CallDuration * 24) & ":" & Format((CallDuration * 1440) Mod 60, "00") & ":" & Format((CallDuration * 86400) Mod 3600, "00") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));
0
 
peter57rCommented:
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")  
0
 
Patrick MatthewsCommented:
Sorry, typo:

SELECT Int(Sum(CallDuration) * 24) & ":" & Format((Sum(CallDuration) * 1440) Mod 60, "00") & ":" & Format((Sum(CallDuration) * 86400) Mod 3600, "00") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));

BTW, I like Peter's suggestion for shortening the expression to become something like this:

SELECT Int(Sum(CallDuration) * 24) & ":" & Format(Sum(CallDuration), "nn:ss") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));
0
 
Gustav BrockCIOCommented:
Minor but important corrections:

SELECT Int(Sum(CallDuration)) * 24 + Hour(Sum(CallDuration)) & Format(Sum(CallDuration), ":nn:ss") AS Total

/gustav
0
 
Patrick MatthewsCommented:
Gustav,

Thanks, got a little sloppy there :)

Regards,

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now