Elapsed Time Calculations

[Frank Gresh]

I have been struggling with calculating elapsed times in a database. All is
well until my rolling total goes over 24 hours, then it won't go any higher
because Access does not have a time format that handles elapsed times
similar to Excel. I have pored over the knowledge base, and have found
several hints, but alas, nothing seems to work. Here is my dilemma:

I am building a vehicle downtime database that tracks the amount of time a
vehicle is out of service for whatever reason. I have no problem
determining the length of time the vehicle is out f service in a query
calculation, it works just fine. The problem comes in when I try to create
a control that gives me a running total, and then a subtotal for each day
and a grand total at the end of the report.

I would be happy to correspond, ship the database and pay for some
assistance on this project as I am about to pull my hair out on what should
be a very simple project.

Your assistance will be appreciated.

[Grant van Dongen]

Frank,

Try the Datediff function to calculate the difference in time intervals. I
have emailed you an example database.
Rgds
Grant van Dongen

Frank Gresh wrote in message <3m2A2.16697$wt4....@news.rdc1.tx.home.com>...

[Frank Gresh]

Thank Grant for saving the day.

I'll be happy to post the code here with Grant's approval.

[Kurt A. Fisher]

Frank Gresh wrote in message <3m2A2.16697$wt4....@news.rdc1.tx.home.com>...
>I have been struggling with calculating elapsed times in a database. All
is
>well until my rolling total goes over 24 hours, then it won't go any higher
>because Access does not have a time format that handles elapsed times
>similar to Excel.

'----------------------------------------------
Function GetElapsedTime(dblInterval As Double) As String
'Returns a formatted string showing elapsed time
' dblInterval may be the difference between any two VBA dates.
' The sum or difference between two VBA dates returns an double number,
' where the integer portion is the number of days and the fractional
' portion is the part of one day.
' Function returns correct elpased time up to two billion seconds or
' approximately 68 years. Causes runtime error for ages over 68 years.

'Source:
'INF: Functions for Calculating and Displaying Date/Time Values
'Article ID: Q88657
'Copyright (c) Microsoft Corporation. 1997.
'Variants of this function also appear in Microsoft's NeatCode.mdb and
'in Getz's VBA Developer's Handbook as function FormatInterval.

Dim totalhours As Long
Dim totalminutes As Long
Dim totalseconds As Long
Dim Days As Long
Dim Hours As Long
Dim Minutes As Long
Dim Seconds As Long

Days = Int(CSng(dblInterval))
totalhours = Int(CSng(dblInterval * 24))
totalminutes = Int(CSng(dblInterval * 1440))
totalseconds = Int(CSng(dblInterval * 86400))
Hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = Days & " Days " & Hours & " Hours " & Minutes & _
" Minutes " & Seconds & " Seconds "

End Function