Change all number field properties to Field Size=Double simultaneously

[pickymiss]

Trying to avoid carpal tunnel, I am turning to the experts.

I am working on a database for a research study. Tons of fields, none repeating other than the one that ties one table to another. For each day for 12 weeks, I have 21 fields with numbers to store (1,764 fields in multiple tables... Again, the fields each store a discrete data point, there is no repeating information.)

My problem (only myself to blame): When I built all the tables, I set the properties of my number field as: Field Size = Integer, Format = Standard, Decimal Places = 2

My issue: That would not store numbers how we need to see them. I need to change all 1,764 fields to: Field Size = Double.

Please tell me if there is a way to change them all at one time by using some type of modify properties command or utility... "where Data Type = Number and Field Size = Long Integer, Field Size = Double..."

While I am asking, is there a way to do a "replace all" in the field description (again, banging my head on the wall...)?

I am tired, sad, my hand is aching and I haven't even made a dent. :-(

I can't find an answer searching the FAQs, formus or Google after using every keyword combination I can think of.

I'm hoping to "owe someone one" soon...

Thanks,
Monique

 

[Golom]

I'll assume that you are using DAO

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

Set db = CurrentDB()

For Each tfd in db.Tabledefs
    If Left$(tbl.Name, 4) <> "MSys" Then
        For Each Fld in tdf.Fields
            If Fld.Type = dbInteger Or Fld.Type = dbLong Then
               db.Execute "ALTER TABLE [" & tdf.Name & "] " & _
                          "ALTER COLUMN [" & Fld.Name & "] " & _
                          "DOUBLE"
            End If
        Next
    End If
Next

That does every table in the database except system tables and every Integer or Long field in the tables.

 

[Golom]

Oops ... that should be

For Each [red]tdf[/red] in db.Tabledefs