Summing hours

[pascal]

When you sum for example 23:00:00 + 04:00:00 this gives as result 03:00:00. How can I sum hours that go above 24 hours like in this case I should become 27:00:00 hours.
Thank you very much.

 

[Alexandre]

Your problem comes from the fact that Date/time data type were meant to store Dates and / or times and not elapsed period of time.
You ll have to do the calculations when needed (don t sore then result).

From the KB:

Calculating Time DataBecause a time value is stored as a fraction of a 24-hour day, you may receive incorrect formatting results when you calculate time intervals greater than 24 hours. To work around this behavior, you can create a user-defined function to ensure that time intervals are formatted correctly. This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual. NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0 To calculate and format time intervals correctly, follow these steps: Create a module and type the following line in the Declarations section if it is not already there:Option Explicit Type the following procedure. NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.
'------------------------------------------------------------------
' This function calculates the elapsed time between two values and
' formats the result in four different ways.
'
' The function accepts interval arguments such as the following:
'
' #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
'
' -or-
'
' [End Time]-[Start Time]
'------------------------------------------------------------------
Function ElapsedTime (Interval)
Dim x
x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
Debug.Print x

x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
& " Minutes:Seconds"
Debug.Print x

x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
& " Hours:Minutes:Seconds"
Debug.Print x

x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
& " Hours " & Format(Interval, "nn") & " Minutes " & _
Format(Interval, "ss") & " Seconds"
Debug.Print x

End Function

Type the following line in the Debug window (or the Immediate window), and then press ENTER: ? ElapsedTime(#6/1/93 8:23:00PM#-#6/1/93 8:12:12AM#) Note that the following values are displayed: 43848 Seconds 730:48 Minutes:Seconds 12:10:48 Hours:Minutes:Seconds 0 days 12 Hours 10 Minutes 48 Seconds

Adapt it to add times instead of substracting them.

Alex

[This message has been edited by Alexandre (edited 02-22-2002).]

 

[pascal]

Thank you very much, Alexandre. You were a great help! It works correctly.

 

[pascal]

I've put the following function in my query to sum several hours (type date/time) grouped by another field called "Employee"

Function:
Int(CSng(Sum([Time]) * 24)) & ":" & Format(Interval, "nn:ss")

Now, I've made a report of this query and I want to put the entire sum of the above results on my report, but I always get the message of an invalid data type.

How can this be done right?