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!
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));
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(myto tal)) & ":" & format(mytotal,"nn:ss")
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Gustav,
Thanks, got a little sloppy there :)
Regards,
Patrick
Thanks, got a little sloppy there :)
Regards,
Patrick
FROM tblCalls
WHERE (((tblCalls.UserId)=1000))