var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Access Data Import Issues
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:MM S (hours min seconds)
The issue is that sometimes, the values are double digit hours. And in some cases its reported as MM S (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
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!
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
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.)
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)
Have a nice day!