[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Access: SUM on Date/Time fields

Posted on 2008-11-10
Medium Priority
Last Modified: 2012-05-05

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

Call duration is like this:

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?


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

Open in new window

Question by:Zaurb
  • 3
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22921439
SELECT Int(CallDuration * 24) & ":" & Format((CallDuration * 1440) Mod 60, "00") & ":" & Format((CallDuration * 86400) Mod 3600, "00") AS Total
FROM tblCalls
WHERE (((tblCalls.UserId)=1000));
LVL 77

Expert Comment

ID: 22921471
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")  
LVL 93

Accepted Solution

Patrick Matthews earned 600 total points
ID: 22921514
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));
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 22925563
Minor but important corrections:

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

LVL 93

Expert Comment

by:Patrick Matthews
ID: 22929051

Thanks, got a little sloppy there :)



Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question