Execute Function From Query

[RangerFan]

I am trying to determine elapsed time using the function listed below. It works fine until the time crosses over into another day. I am trying to execute this function from an Access Query. I need to pass the starting date and time and the ending date and time, however I am having a problem determining the correct syntax.

This is what I have so far TimeElapsed:HoursAndMinutes([CompTime]-[ItemTime])but am having a problem getting the date in there.

Any help would be greatly appreciated.


Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

' 1440 = 24 hrs * 60 mins
totalminutes = Int(CSng(interval * 1440))
minutes = totalminutes Mod 60

' 86400 = 1440 * 60 secs
totalseconds = Int(CSng(interval * 86400))
seconds = totalseconds Mod 60

' Round up the minutes and adjust hours
If seconds > 30 Then minutes = minutes + 1
If minutes > 59 Then hours = hours + 1: minutes = 0
HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

 

[HarleyQuinn]

If you're using a Date/Time field, it seems to work for me as you'll be expecting.

What is actually going wrong for you?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

You could even use this lovely formula to achieve the result you want...

TimeElapsed2: Format(Int(DateDiff("n",[ItemTime],[CompTime])/60),"00") & ":" & Format(Int(DateDiff("n",[ItemTime],[CompTime])-(Int(DateDiff("n",[ItemTime],[CompTime])/60)*60)),"00")

It's a bit Urgh! though, so if I'd only try it to make sure the UDF is working correctly.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[RangerFan]

Your reference to date/time tipped me off. I was using date and time as two seperate fields. I see where I was going wrong. Thank you for the tip. Much appreciated.