Expert: Geoff - 2/21/2005

Question
How do I create a MS Access macro that will change a field's data type from text to a date/time value without going through the "design view" of the table.

Answer
Hi Michael

I have written a small function which will do this: to run this from a macro, simply paste this into a new module and then use the RunCode command referencing this function.

Caveate: There is no error handling in the code, and no input data validation.....

I hope this helps

Kind regards

Geoff


Function ChangeFieldDataType()

Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field

   'Ensure there is a reference to MS DAO v xx
   'Data types
       'Type = 8 = "Date/Time"
       'Type = 10 = "Text"
   'Edit the table name and field name required.

   Set db = CurrentDb
   For Each tdf In db.TableDefs
  If tdf.Name = "tblYourTableName" Then
          For Each fld In tdf.Fields
        If fld.Name = "YourFieldName" Then
           fld.type = 10
           Exit Sub
        End If        
        Next
  End If
   Next

End Function