How can I convert a whole table of string dates to a date format

At my last contract we had to import monthly disks from a healthcare provider. The dates were in a string format such as "mmddyyyy" or "yyyymmdd". Well if you are trying to do calculations based on dates, Access will not recognize these. So I created the following functions.

It will convert the following formats:

"mmddyy", "mmddyyyy", "yyyymmdd", "yyyy-mm-dd"

You can increase the formats by adding cases to the Select case statement in the ConvertDates function.

Just copy both functions below and put them in a module. That's it.

To convert one date, just call the ConvertDate function, passing it the format and the date: for example
Call ConvertDate("mmddyyyy", "052600")

If you want to convert all the records in a table, Use the ConvertTableDates Function, passing it the table name, field name, and format: for example
Call ConvertTableDates("tblImport", "StartDate", "yyyymmddd")
When completed, go to table design and change the data type to Date/Time

You can just call it from the immediate window, or call it from a button click.

Hope this is helpful, let me know if you have problems, or need help with a format not listed.



Public Sub ConvertTableDates(strTable As String, strField As String, strFormat As String)
Dim db As Database
Dim rs As Recordset
Dim strSQL As String, strDate As String
strSQL = "SELECT " & strField & " FROM " & strTable

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount > 0 Then
With rs
.MoveFirst
Do Until .EOF
strDate = ConvertDate(strFormat, rs(strField))
.Edit
rs(strField) = strDate
.update
.MoveNext
Loop
End With
Else
Exit Sub
End If

rs.CLOSE
db.CLOSE

MsgBox "Process Complete"
End Sub

Function ConvertDate(strFormat As String, strDate As String) As Date
Dim newDate As String

Select Case strFormat
Case "yyyy-mm-dd"
newDate = Mid(strDate, 6, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4)
Case "yyyymmdd"
newDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4)
Case "mmddyyyy"
newDate = Left(strDate, 2) & "/" & Mid(strDate, 3, 2) & "/" & Right(strDate, 4)
Case "mmddyy"
newDate = Left(strDate, 2) & "/" & Mid(strDate, 3, 2) & "/" & Right(strDate, 2)
End Select
ConvertDate = newDate
End Function

==========================

Jim Lunde
jimlunde@gmail.com