MS Access Query Question - Time Chart

How do I calculate total time?

Hiroyuki TamuraField EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

have you tried using the Sum() function in a query?
It's probably going to be a little more complicated than that.  

It appears that the elapsed time is stored as a Date data type.  The Date data type represents a point in time, NOT elapsed time.  So if your sum ends up as more than 24 hours, you end up with a date value.  So, the result could look like:

12/31/1899 04:30 AM

if your total is 28.5 hours.


1/11/1900 04:30 AM

if your total is 52.5 hours.

You would need to take the days part of the Date field and multiply them by 24.  Just remember, you can't put them back into a Date data type or you'll end up with a date again.

So, your choice is to change the data type to integer or long integer and store minutes which are easy to convert to hours and sum or you can  define the data as the Currency data type (but display as standerd not currency) and then you would be storing hours.fraction  OR you could define two columns and store hours in 1 and minutes in the other.  This third option will be easiest to use for data entry because you can use two text boxes with a label containing a colon between them so it looks like a time field.

Let us know what you want to do and we'll help with the expression you need.
Sum([Time to Abandon) * 86400 will be the total number of seconds
Gustav BrockCIOCommented:
Sum the values in a query:

Select Sum([Time to Abandon]) From Table1

Open in new window

If the sum may exceed one day, use the function below:

Select FormatHourMinuteSecond(Sum([Time to Abandon])) From Table1

Open in new window

Public Function FormatHourMinuteSecond( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
' 2014-06-17. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinuteSec  As String
  Dim strHours      As String
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute and second count when needed.
  strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
  strHours = strHour & strSeparator & strMinuteSec
  FormatHourMinuteSecond = strHours
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hiroyuki TamuraField EngineerAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.