Code to Change all Date Fields to Text 2

[Knicks]

I am trying to create code that looks at the entire database (except MsSys tables) and changes the data type from date to text in all tables. The database is ultimately going to be upsized into SQL and date fields when out of range cause the upsizer to fail.

Any help would be appreciated,
Thanx

 

[SkipVought]

Hi,

Check out the Format function.

Exp1: Format([DateField],"yyyy/mm/dd")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Remou]

If I understand you, you need to go through the TableDefs collection and check each Field for Type (property).

 

[Knicks]

Remou,

That is correct.

 

[Golom]

Something like this?

Sub ChangeDateToText()
    Dim db                          As DAO.Database
    Dim tdf                         As DAO.TableDef
    Dim fld                         As DAO.Field
    Dim SQL                         As String
    Set db = DAO.DBEngine(0).OpenDatabase(".\ChapsMast.Mdb")

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" _
           And Left(tdf.Name, 1) <> "~" _
           And Len(tdf.Connect) = 0 Then
           
            For Each fld In tdf.Fields
                If fld.Type = dbDate Then
                    SQL = "ALTER TABLE [" & tdf.Name & "] " & _
                          "ALTER COLUMN [" & fld.Name & "] Text(50)"
                    db.Execute SQL
                End If
            Next
        End If
    Next
End Sub

 

[Knicks]

Golom,

Thank you, this works great. I just needed to change the line

Set db = DAO.DBEngine(0).OpenDatabase(".\ChapsMast.Mdb")

to

set db = currentdb()

I will probably run this from outside of the database but I think I can manage in input string to tell it which database to make the date changes to.