Calculating Elapsed Time

[tnbubbachandan]

Hopefully, someone can help me with the following. I have a table with two fields - Closed & Created. Both are Date/Time General formats. I tried creating a module to calculate the elapsed time between both see below. I have tried putting this expression in a report as =GetElapsedTime([Closed]-[Created]) The report comes with an error. Can anyone tell me what I am doing wrong or a better way to do this?

Option Explicit
Function GetElapsedTime(interval)

Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & " Minutes " & Seconds & " Seconds "

End Function

 

[GGib7711]

Your function runs fine in my Access 2000 database.

What error did you encounter?

 

[Mile-O]

I suspect what may cause the function not to work "properly" would be Null values in either the Closed or Created field.

 

[tnbubbachandan]

It works, BUT....

Hmmm, for some reason it started to work. Now, how can I get it to exclude hours from 5:00:00 PM thru 8:00:00 AM?

 

[raskew]

We just got done beating this subject to death. See here http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58003 .

HTH- Bob