The date/time data type in Access represents a point in time, not a time
duration , so, as Karl has pointed out, you could to import the value as a
text data type. If you do need to do time arithmetic on the values you can
convert the value to seconds with a function like this:
Public Function ConvertToSeconds(strTime As String) As Long
Dim aTimes(2)
aTimes(0) = Val(Left(strTime, InStr(1, strTime, ":") - 1))
aTimes(1) = Val(Mid(strTime, InStr(1, strTime, ":") + 1, 2))
aTimes(2) = Val(Right(strTime, 2))
ConvertToSeconds = (aTimes(0) * 3600) + _
(aTimes(1) * 60) + aTimes(2)
End Function
So your example of '150:27:50 ' would return 541670. After doing any
arithmetic on the values converted to seconds you can then convert the result
back to your time format as a string with:
lngSeconds\3600 & ":" & lngSeconds\60 Mod 60 & ";" & lngSeconds Mod 60
Alternatively the following function, which is really designed for a rather
different purpose, would as it happens also return your current date/time
values to a string of the required format:
Public Function TimeElapsed(dblTotalTime As Double, _
Optional blnShowDays As Boolean = False) As String
Const HOURSINDAY = 24
Dim lngDays As Long
Dim lngHours As Long
Dim strMinutesSeconds As String
' get number of days
lngDays = Int(dblTotalTime)
' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")
'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' return total elapsed time either as total hours etc
' or as days:hours etc
If blnShowDays Then
lngHours = lngHours - (lngDays * HOURSINDAY)
TimeElapsed = lngDays & ":" & Format(lngHours, "00") &
strMinutesSeconds
Else
TimeElapsed = Format(lngHours, "#0") & strMinutesSeconds
End If
End Function
Omit the optional second argument when calling it as you don't want to show
the days. The function would also enable you to do time arithmetic on your
date/time values e.g. in a query
ElapsedTime(SUM([YourTimeField])) would return the sum of the times as a
string in the desired format.
Ken Sheridan
Stafford, England
I have an excel file that I'm importing to Access. This field consists of
"time"...hours, minutes, seconds. The format in excel is set to "Custom" -
[h]:mm:ss.
The data elements look like 150:27:50, 2:30:25, etc...
The time stays the same for some records but others change. For
example...150:27:50 becomes 6:27:50 AM when imported.
If someone can help me figure a way to retain the format for all records I
would greatly appreciate it.
Thank You,
Lisa W.
