2

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".)

5
  • Bob did you tried to import that time information as text from flat file? is this file is CSV? – AA.SC Sep 18 '14 at 5:48
  • @AA.SC Yes. I tried to import from a text flat file. The fields are delimited by tabs. I actually found on another site a comment from an MS engineer to the effect that MS Access time format simply doesn't allow fractional seconds. So I put all the integers into the time and split off the fractional part of the seconds into a separate field. Kind of ugly, but I haven't thought of a better way. Ideas are appreciated! – bob.sacamento Sep 18 '14 at 17:38
  • It's true, the Date/Time column type in Access does not support fractional seconds. Do you need to keep that degree of precision or would rounding to the nearest second suffice? – Gord Thompson Sep 19 '14 at 20:40
  • One thing I've found is that when importing data from text files, Access uses roughly the first 100 rows of data to build up a preview of what it should expect from the entire file. If your first 100 rows contain all possible variations of the time column, that gives Access a better chance of reading it right. – Yawar Sep 22 '14 at 0:19
  • @Gord Thompson I don't need all the precision given but, no, rounding wouldn't be close enough. Thanks for the idea. – bob.sacamento Sep 22 '14 at 16:50
1

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 as Text and then populate another Long 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 this

ID  hms_time       tms_time 
--  -------------  ---------
 1  00:00:00.0001
 2  23:59:59.9999
 3  00:00:00.0000
 4  03:25:45.6789
 5  22:57:40.0000

If you create a VBA function in an Access Module like this

Option Compare Database
Option Explicit

Public Function Hms2Tms(hms As Variant) As Variant
    ' convert "hh:mm:ss.dddd" String
    '     to Long Integer tenths-of-milliseconds (0..863999999)
    Dim arr() As String, rtn As Long

    If IsNull(hms) Then
        Hms2Tms = Null
    Else
        arr = Split(hms, ":", -1, vbBinaryCompare)
        rtn = CLng(arr(0)) * 36000000 + CLng(arr(1)) * 600000
        arr = Split(arr(2), ".", 2, vbBinaryCompare)
        rtn = rtn + CLng(arr(0)) * 10000 + CLng(arr(1))
        Hms2Tms = rtn
    End If
End Function

then you can use the query

UPDATE TimesTable SET tms_time = Hms2Tms([hms_time])

to calculate the numeric equivalents

ID  hms_time       tms_time 
--  -------------  ---------
 1  00:00:00.0001          1
 2  23:59:59.9999  863999999
 3  00:00:00.0000          0
 4  03:25:45.6789  123456789
 5  22:57:40.0000  826600000

You can also use the following VBA function to convert a numeric "tms" time into an "hms" string:

Public Function Tms2Hms(tms As Variant) As Variant
    ' convert Long Integer tenths-of-milliseconds (0..863999999)
    '     to "hh:mm:ss.dddd" String
    Dim hh As Long, mm As Long, ss_dddd As Currency, remainder As Long, rtn As String

    If IsNull(tms) Then
        Tms2Hms = Null
    Else
        hh = Int(CCur(tms) / 36000000)
        rtn = Format(hh, "00")
        remainder = tms - hh * 36000000
        mm = Int(CCur(remainder) / 600000)
        rtn = rtn & ":" & Format(mm, "00")
        remainder = remainder - mm * 600000
        ss_dddd = CCur(remainder) / 10000
        rtn = rtn & ":" & Format(ss_dddd, "00.0000")
        Tms2Hms = rtn
    End If
End Function
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.