Based on your two samples, I assume that this is military time, being imported as a numeric datatype. Hence, for midnight, Access is converting 0000 to 0. So I think what you have to do is to look at the length of the original value and parse it into standard military/24 hour format time. From there you can use it to do your normal time calculations.
Where the time you're importing is
OriginalTime and the parsed version is
StandardMilitaryTime.
- Private Sub ConvertToMilitaryTime_Click()
-
-
If Len(Me.OriginalTime) = 1 Then
-
Me.StandardMilitaryTime = "00:0" & Me.OriginalTime
-
End If
-
-
If Len(Me.OriginalTime) = 2 Then
-
Me.StandardMilitaryTime = "00:" & Right(Me.OriginalTime, 2)
-
End If
-
-
If Len(Me.OriginalTime) = 3 Then
-
Me.StandardMilitaryTime = "0" & Left(Me.OriginalTime, 1) & ":" & Right(Me.OriginalTime, 2)
-
End If
-
-
If Len(Me.OriginalTime) = 4 Then
-
Me.StandardMilitaryTime = Left(Me.OriginalTime, 2) & ":" & Right(Me.OriginalTime, 2)
-
End If
-
End Sub
-
I need too emphasize that I've been fighting a headache the size of Texas, and am having a hard time concentrating, and that I've only done limited testing of this code, so you need to run some tests on a wide range of data. I think, however, assuming the data you're importing is being formatted consistently, that this will work. I have run tests using the resultant military "times" and they do work correctly when using them in functions such as
DateDiff and
DateAdd.
Welcome to TheScripts!
Linq
;0)> 
3
