Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009

    Unanswered: Access Data Import Issues

    Hello all,

    Running into an issue importing some data to Access. Using a Phone call center tool to export Call times, counts, availabilty etc into Access.

    The data is a monthly summary. So some of the colums are reported as HHH:MMS (hours min seconds)

    The issue is that sometimes, the values are double digit hours. And in some cases its reported as MMS (only minutes and seconds)

    So when I import this, Ive tried to setup a mask or a format and it either ruins the data or i get rejected data as it does not fit the format.

    Does anyone have any clues or ideas on how to import this data and still be able to do calculations on it?

    Heres some examples of the data:

    Agent Name ACD Calls Avg ACD Time Avg ACW Time
    UserA 185 5:07 14:39 64 12 0
    USERB 94 10:52 :35 42 16 1

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    How can you dertermine if 10:45 means ten hours forty-five minutes or ten minutes and fourty-five seconds? and what about :35 as in the second line in your example?
    Have a nice day!

  3. #3
    Join Date
    May 2009
    SO thats the hard part with this crappy tool that does the exporting.

    10:45 would be 10 min 45 seconds as its only broken down to two levels

    :35 would be 35 seconds as its only at the first level.

    Somehow I need to get access to import that data and review it and have a default value of


    and have it replace the values from right to left

    So if 10:45 comes in the import it fills from right to left


    This is where im stuck

  4. #4
    Join Date
    Apr 2011
    Maybe use a query and an expression to combine the times if they're held in different fields?

    Time: [Hours]&[Seconds] (Set the input mask to 00:00:00)
    so in the hours field the data would be 10:45 in the seconds field :35 = 10:45:35

    (not sure if you'll be able to calculate on it later though.)

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    Then there is some hope. You can use this function to convert the input to a proper time value:
    Public Function CastToTime(ByVal TimeString As Variant) As Variant
        Dim var As Variant
        Dim strH As String
        Dim strM As String
        Dim strS As String
        strH = "00"
        strM = "00"
        var = Split(TimeString, ":")
        strS = IIf(IsNumeric(var(UBound(var))), var(UBound(var)), "00")
        If UBound(var) > 0 Then strM = IIf(IsNumeric(var(UBound(var) - 1)), var(UBound(var) - 1), "00")
        If UBound(var) > 1 Then strH = IIf(IsNumeric(var(UBound(var) - 2)), var(UBound(var) - 2), "00")
        CastToTime = CDate(strH & ":" & strM & ":" & strS)
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts