I have raw data in .csv files which I import into Access daily. The date field contains "5:44pm MON FEB 23, 2009" or "10:01am TUE FEB 1, 2009" (notice the single and double position day number). I cannot sort using this date. So my options are to modify the .csv files with vba (pre import) or modify it on import, or do a post update query to each table (without my data normalized, I have way too many tables to do this on.... :)).

Any takers on how I should approach this, and some sample code?

Help is much appreciated!
Kurtis

You have two areas which may give you problems, the hour in the time, and the day in the date.

You asked specifically about the date, assuming that the comma "," is six characters back from the right-hand side, then the unit value of the day will always be at the seventh character position. If a date is correctly formatted then a "1" or "2" will be at the eighth position. However if the eighth position contains a space " ", then the space needs replacing with a zero "0".

I suggest you set up a simple form with just two controls, a text box "txtSampleData" and set its default value to: "5:44pm MON FEB 23, 2009" the other control should be a command button, "btnTest" create an event procedure for the button click event and in this event procedure add the following code:

MsgBox " >>> " & Right(txtSampledata, 8) and from memory I believe the message box will return "2" you may need to change the 8 to 7 or 9, and my code is from memory so it may not work without some fiddling.

Hi -

That's a good-un! In playing around with this, testing both CDate() and DateValue() functions, it appears
that in order for either of these functions to convert your text date to datetime data format, it (text date)
must be converted to something Access will read/recognize, e.g.:

x = "5:44pm MON FEB 23, 2009" -- converts to
"FEB 23, 2009 5:44 pm"

y = "10:01am TUE FEB 1, 2009" -- converts to
"FEB 1, 2009 10:01 am"

That process involves:
1) Getting rid of the 3-character weekday ('MON', 'TUE', etc.) designator.
2) Moving the time from the front to the rear of the string.
3) Inserting a space preceeding the am/pm designator.

So, from the debug (immediate) window:

x = "5:44pm MON FEB 23, 2009"
y = "10:01am TUE FEB 1, 2009"

-----------------------------------------------------------------------------------------

? cdate(mid(x, Instr(x, " ") + 5) & " " & left(x, instr(x, " ") -3) & " " & mid(x, instr(x, " ")-2, 2))
2/23/2009 5:44:00 PM

--- and to prove that it's in fact in datetime data format
? cdbl(cdate(mid(x, Instr(x, " ") + 5) & " " & left(x, instr(x, " ") -3) & " " & mid(x, instr(x, " ")-2, 2)))
39867.7388888889

-----------------------------------------------------------------------------------------

? cdate(mid(y, Instr(y, " ") + 5) & " " & left(y, instr(y, " ") -3) & " " & mid(y, instr(y, " ")-2, 2))
2/1/2009 10:01:00 AM
--- and to prove that it's in fact in datetime data format
? cdbl(cdate(mid(y, Instr(y, " ") + 5) & " " & left(y, instr(y, " ") -3) & " " & mid(y, instr(y, " ")-2, 2)))
39845.4173611111

-----------------------------------------------------------------------------------------

Breaking it down a little further:
--- this returns the date
? mid(y, instr(y, " ") + 5)
FEB 1, 2009

--- this returns the time
? left(y, instr(y, " ") -3)
10:01

--- this returns the am/pm designator
? mid(y, instr(y, " ")-2, 2)
am

Insert the necessary spaces and wrap it all in the cDate() function and it returns a true datetime data format.

HTH - Bob

I agree with Uncle Gizmo's assessment. Normally we would use Format() but given the inconsistency, this is something we need to fix first.

Off the top of my head:

Private Function PrepFormat(sInput As String) As String

Dim s As String
dim t as String

s = Mid$(sInput, 16, 2)
Select Case s
Case "1,", "2,","3,","4,","5,","6,","7,","8,","9,"
t = Left$(sInput, 16) & "0" & Right$(sInput, Len$(sInput)-16)
End Select

s = Mid$(sInput, 1, 2)

Select case s
Case "1:", "2:", "3:", "4:", "5:", "6:", "7:", "8:", "9:"
t = "0" & sInput
End Select

PrepFormat = t

End Function

This function, of course, only works if the formatting is consistent throughout.