MS Access Query Question - Time Chart

Hiroyuki Tamura
Hiroyuki Tamura used Ask the Experts™
How do I calculate total time?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

have you tried using the Sum() function in a query?
Distinguished Expert 2017
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.
Top Expert 2014
Sum([Time to Abandon) * 86400 will be the total number of seconds
Most Valuable Expert 2015
Distinguished Expert 2018
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

Hiroyuki TamuraField Engineer


Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial