Solved

# Format SUM function

Posted on 2013-01-09
512 Views
Greetings,

I have a database that has a field which contains seconds.  I am doing a Group By and using SUM on the seconds field.  I need to format the SUM results into HH:MM:SS.

Select Sum([Field1]),
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'

MyDuration I need in HH:MM:SS

Thanks so much
0
Question by:jchauncey60
• 2

LVL 61

Assisted Solution

mbizup earned 250 total points
ID: 38760782
Try this:

``````SELECT q.SumOfField1, Format(Int(q.[MyDuration]/3600),"00") & ":" & Format(Int((q.[MyDuration]-(Int(q.[MyDuration]/3600)*3600))/60),"00") & ":" & Format(((q.[MyDuration] Mod 60)),"00") AS ElapsedTime
FROM
(
Sum([Field1]) AS SumOFField1,
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'
) q
``````
0

LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 250 total points
ID: 38760804
If that value (Sum([Seconds]) is not going to exceed 24 hours, you could also do:

Format(Sum([Seconds])/86400, "hh:nn:ss")

dividing the sum by 86400 (the number of seconds in a day) will give you the decimal equivalent of a time value as stored in the database.
0

Author Comment

ID: 38760824
Solution #2 works for this problem.  Solution #1 is the best for generic applications. Thanks for the quick response.
0

LVL 61

Expert Comment

ID: 38760954
0