Calculating # of Hours from Date and Time Fields 1

[InspectorJoe]

We have a short date1 field (00/00/00) and a short time1 field (24:00). We need to compare date1 and time1 to the current date and time when a date2 field is null. The result will be the number of hours from the date1 and time1 to current date and time.

Example:
Date1: 01/01/01 Time1: 17:00
Current Date: 01/03/01 Current Time: 8:05
Date2: Null
Results: 39 hrs and 5 minutes or 39.08 hrs
- or -
Date1: 01/01/01 Time1: 17:00
Current Date: 01/01/01 Current Time: 19:15
Date2: Null
Results: 2 hrs and 15 minutes or 2.25 hrs

I know what I want, I just don't know how to get there.

Joe.Aleshire@dys.state.oh.us

 

[CosmoKramer]

Joe,

Here's a way to do it when the timestamp you are comparing to is in the General Date format:

Define a public function with this code:

Public Function TimeElapsed(dIn As Variant, dOut As Variant) As String
    If dOut - dIn > 0 Then
        TimeElapsed = CStr(Int(Format(dOut - dIn, "General Number"))) & " Day(s) " & DatePart("h", dOut - dIn) & " Hour(s) " & DatePart("n", dOut - dIn) & " Minute(s)"
    Else
        TimeElapsed = ""
    End If
End Function

In your query, refer to this function by creating an expression similar to this:

ElapsedTime: TimeElapsed([YourDateTimeField],Now())

This will return the calculated field in this format:

0 Day(s) 2 Hour(s) 56 Minute(s)

Hope this helps.....

 

[InspectorJoe]

I think that will work, however, the date and time are already being captured in separate fields. Is there any way to take the short date and short time fields and combine them to a general date that includes both date and time? Then I could implement the solution you suggested. Thanks.

 

[CosmoKramer]

OK, try this:
Create a calculated field in your query to combine the date and time fields:

DateAndTime: [DateField] & " " & [TimeField]

Change the expression that runs the function to:

ElapsedTime: TimeElapsed(CVDate([DateAndTime]),Now())

 

[InspectorJoe]

Worked like a charm! Thanks so much.