I need to import data from a flat file to MS Access. Part of the data is time information in the format HH:MM:SS.SSSS (i.e. 24-hour time, fractional seconds). I have tried all of the standard time formats, and have monkeyed around with input masking, but Access keeps complaining that I am violating formats and won't import my data. Anybody know how to do this? Thanks.
(BTW, the "date-format" tag was the closest I could find to something like "time format".)
Best Answer
As you have discovered, the
Date/Time
column type in Access does not support fractional seconds. If you need to preserve the precision (and not round to the nearest second) then you'll need to import the time values asText
and then populate anotherLong Integer
column with the times converted to tenths-of-milliseconds (which I have abbreviated to "tms"):After the initial import of the times as
Text
you will have something like thisIf you create a VBA function in an Access Module like this
then you can use the query
to calculate the numeric equivalents
You can also use the following VBA function to convert a numeric "tms" time into an "hms" string: