Addings HRs:Mins over 1000000000:00??? Overflow error??

[jw5107]

Below is code I am working with:

Sub TestConvertDateToStringTime()
MsgBox "#02:20#" & "-> " & ConvertDateToStringTime(#2:20:00 AM#, "hh:mm")
Debug.Print "02:20" & "-> " & ConvertDateToStringTime(#2:20:00 AM#, "hh:mm")
MsgBox "#12/31/1899 9:02:00 PM#" & "-> " & ConvertDateToStringTime(#12/31/1899 9:02:00 PM#, "hh:mm")
Debug.Print "12/31/1899 9:02:00 PM" & "-> " & ConvertDateToStringTime(#12/31/1899 9:02:00 PM#, "hh:mm")
MsgBox "#1/1/1900 02:20#" & "-> " & ConvertDateToStringTime(#1/1/1900 2:20:00 AM#, "dd:hh:mm")
Debug.Print "1/1/1900 02:20" & "-> " & ConvertDateToStringTime(#1/1/1900 2:20:00 AM#, "dd:hh:mm")
MsgBox "#1/4/1900 8:59:00 AM#" & "-> " & ConvertDateToStringTime(#1/4/1900 8:59:00 AM#, "h:nn")
Debug.Print "1/4/1900 8:59:00 AM" & "-> " & ConvertDateToStringTime(#1/4/1900 8:59:00 AM#, "h:nn")
MsgBox "#1/4/1900 8:59:00 AM#" & "-> " & ConvertDateToStringTime(#1/4/1900 8:59:00 AM#, "dd:hh:nn")
Debug.Print "1/4/1900 8:59:00 AM" & "-> " & ConvertDateToStringTime(#1/4/1900 8:59:00 AM#, "dd:hh:nn")
End Sub
-
Function ConvertDateToStringTime(StringDate As Date, FormattedTime As String) As String
On Error GoTo Err_ConvertDateToStringTime
Dim newdate As Date
Dim ldays As Variant
Dim lhours As Variant
Dim lminutes As Variant
newdate = StringDate
ldays = Int(CDbl(newdate))
If Left(FormattedTime, 1) = "d" Then
FormattedTime = Mid(FormattedTime, InStr(FormattedTime, ":") + 1)
If ldays = 0 Then
ldays = Null
Else
ldays = Format(ldays, "00") & ":"
End If
ConvertDateToStringTime = ldays & Format(newdate, FormattedTime)
Else
lhours = hour(newdate)
lhours = Format(lhours + ldays * 24, "00")
lminutes = Format(minute(newdate), "00")
ConvertDateToStringTime = lhours & ":" & lminutes
End If
Exit_ConvertDateToStringTime:
Exit Function
Err_ConvertDateToStringTime:
If Err.Number = 13 Then 'type mismatch
MsgBox "Not recognized as a date. Check format"
Resume Exit_ConvertDateToStringTime
Else
MsgBox Err.Description
Resume Exit_ConvertDateToStringTime
End If
End Function

Using this in a query:
FleetTotalTAT: ConvertDateToStringTime(Sum(convertStringTimeToDate([EOMTAT])),"HH:MM")

The problem is I am adding Total Air Time of aircraft that have huge hrs on them. Some are like 76256:25. Is there any way for the code above to ignore the over flow error and keep doing the math and add it all up??

Thanks for the help!!
jw

 

[PHV]

Which line of code is highlighted in debug mode when the overflow error is raised ?
Where is the convertStringTimeToDate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jw5107]

When the overflow error message pops up - it just has an OK box to click. I have to click like 1,000 times in order to get it to go away. Then the query opens with the results like 00:00 in the records it had trouble summing on.

ConvertStringTimeToDate function:

Sub TestConvertStringTimeToDate()
MsgBox "02:30" & "-> " & ConvertStringTimeToDate("02:30")
Debug.Print "02:30" & "-> " & ConvertStringTimeToDate("02:30")

MsgBox "23:02" & "-> " & ConvertStringTimeToDate("23:02")
Debug.Print "45:02" & "-> " & ConvertStringTimeToDate("45:02")

MsgBox "128:59" & "-> " & ConvertStringTimeToDate("128:59")
Debug.Print "128:59" & "-> " & ConvertStringTimeToDate("128:59")

MsgBox "02:02:02" & "-> " & ConvertStringTimeToDate("02:02:02")
Debug.Print "02:02:02" & "-> " & ConvertStringTimeToDate("02:02:02")
End Sub

Function ConvertStringTimeToDate(StringDate As String) As Date
On Error GoTo Err_ConvertStringTimeToDate
Dim newdate As Double
Dim tempstring As String
Dim ldays As Integer
Dim lhours As Integer
Dim lminutes As Integer
tempstring = StringDate
ldays = Left(tempstring, InStr(tempstring, ":") - 1)
tempstring = Mid(tempstring, InStr(tempstring, ":") + 1)
If InStr(tempstring, ":") <> 0 Then 'a second colon exists
lhours = Left(tempstring, InStr(tempstring, ":") - 1)
lminutes = Mid(tempstring, InStr(tempstring, ":") + 1)
Else
lhours = ldays
ldays = 0
lminutes = Mid(tempstring, InStr(tempstring, ":") + 1)
End If

If lhours > 23 Then
ldays = lhours \ 24 'integer division
lhours = lhours - (ldays * 24)
End If

If lminutes > 59 Then
MsgBox "Minutes must be less than 60"
Exit Function
End If

newdate = ldays + CDate(lhours & ":" & lminutes)
ConvertStringTimeToDate = CDate(newdate)

Exit_ConvertStringTimeToDate:
Exit Function

Err_ConvertStringTimeToDate:
If Err.Number = 13 Then 'type mismatch
MsgBox "Not recognized as a date. Check format"
Resume Exit_ConvertStringTimeToDate
Else
MsgBox Err.Description
Resume Exit_ConvertStringTimeToDate
End If
End Function

 

[PHV]

Replace all the As Integer to As Long

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

I'm sure that I am just not undestanding the issue, but would you please explain what you are really attempting to do with these routines? I "get" that they are complimentary conversions -what I don't get is the necessity to do the trivial and tedious manipulation. RE your sample, at least the one routine is used in an aggregate query which is generating the sum of "times". But my confusion starts with the though of storing time as a string to begin with.

To use your example:

? ConvertStringTimeToDate("76256:25")
9/11/1908 8:25:00 AM 


? Format(#9/11/1908 8:25:00 AM#, "dd:hh:nn")
11:08:25

SEEMS to adequately illustrate that the times could reasonably be stored (directly) in the date-Time format and simply summed and formatted using intrinsic functions.




MichaelRed

 

[jw5107]

The time is a text field. I am linked up to an Oracle table and the time field that I am doing the math on is a text field.
By changing everything to Long instead of integer. This seemed to do the trick!!!

WORD!!! - to PHV!!

Thanks,
jw

 

[MichaelRed]

... and "Oracle" doesn't support Double (pseudo Data-Time) fields? Or is it "someone-else's" data and you just use it? ... but then why would you need both conversions?



MichaelRed