Solved

# MS Access: SUM on Date/Time fields

Posted on 2008-11-10
1,836 Views
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));
``````
0
Question by:Zaurb

LVL 92

Expert Comment

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

LVL 77

Expert Comment

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

LVL 92

Accepted Solution

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

LVL 48

Assisted Solution

Minor but important corrections:

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

/gustav
0

LVL 92

Expert Comment

Gustav,

Thanks, got a little sloppy there :)

Regards,

Patrick
0

## Featured Post

### Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…