INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

How can I convert a whole table of string dates to a date format by jimmythegeek
Posted: 26 May 00 (Edited 27 Sep 07)

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

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close