MS Access: SUM on Date/Time fields

Posted on 2008-11-10
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
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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

    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 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));
    LVL 48

    Assisted Solution

    by:Gustav Brock
    Minor but important corrections:

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

    LVL 92

    Expert Comment

    by:Patrick Matthews

    Thanks, got a little sloppy there :)



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    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…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now