Designing Date and Time fields to total up hours (1 Viewer)

[sal]

I am building a database to hold data for fish weir counts. Some weirs run 24 hours and others only trap for partial days. Right now I have a Date field and two time fields: Trap_In and Trap_Out. The idea being that much of the data will need to be totaled by Date, but that trapping hours will also need to be totaled.

I have built a check box control, which when TRUE auto fills the Trap_In with 0 and Trap out with 1 (for the fraction of the 24 hour day). But the result for the Trap_Out value is not behaving as I would like.

I realize that 24 hours is equal to 0 for the next day but I am getting a 0 hour with a date, 12/31/1899.

Am I designing my fields incorrectly? Should I merge Date and Time? If so, I do not see and Date Time alternatives that use 24 hour time.

 

[wjburke2]

I used a start and end time in date/time format. Then I use a function to calculate the number of days/hours/min between the start and end time. Hope this helps maybe it will save a little diging..

Public Function ElapsedTimeString(dateTimeStart As Date, _
dateTimeEnd As Date, _
RetType As String) _
As String

'*************************************************************
' Returns the time elapsed between a starting Date/Time and
' an ending Date/Time formatted as a string:
'if RetType = s it looks like this
' "10 days, 20 hours, 30 minutes, 40 seconds".
'else it returns a decimal hours and 10th of hours
' hh.#
'*************************************************************
Dim interval As Double, str As String, days As Variant
Dim Hours As String, minutes As String, seconds As String

Dim lngDays As Long
Dim lngHours As Long
Dim lngMin As Long
Dim dblTime As Double

If IsNull(dateTimeStart) = True _
Or IsNull(dateTimeEnd) = True Then
ElapsedTimeString = 0
Exit Function
End If

interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
lngDays = CInt(days)
Hours = Format(interval, "h")
lngHours = CInt(Hours)
minutes = Format(interval, "n")
lngMin = CInt(minutes)
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", days & "d")
str = str & IIf(days = 0, "", IIf(Hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
str = str & IIf(Hours = "0", "", Hours & "h")
str = str & IIf(Hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
str = str & IIf(minutes = "0", "", minutes & "m")

dblTime = (lngDays * 24 + lngHours + Round(lngMin / 60, 1))

str = IIf(RetType = "S", str, dblTime)

ElapsedTimeString = IIf(str = "", "0", str)

End Function

 

[Pat Hartman]

There is no separate time data type. Time is recorded in conjunction with a date. Internally the date/time is stored as a double precision number with the integer portion representing the number of days since Dec 31, 1899 and the decimal portion representing the fraction of a day. That's why you see D3c 31, 1899 as the date when all you input was the time. The integer value is 0. This is not a problem though. If you use three separate fields Dateonly, timeinonly, and timeoutonly, you can show the start as dateonly + timeiononly and the end as dateonly +timeoutonly. Then you can subtract the calculated timeindate from the calculated timeoutdate to find the elapsed hours.

 

[wjburke2]

You are right Pat. For his question I was thinking a simple general date format would due 1/1/2000 12:34:00 PM. This simplifies the data validation as the user can see the date and time in the same field. Click a button to auto fill with Now would even help fill in the correct format.