Change multiple field's properties from Long Integer to Decimal

[Leonidsg]

Hello,

I was wondering if it is possible to batch edit field properities. I have many fields that are number fields but I need change the properties from long integer to decimal.



Thank you,
Lenny

[rpeare]

on a table or on a form?

either is possible but radically different in approach.

[Leonidsg]

That's a good question. I guess I have a lot number fields on a table which are inputted via a form. If the number is 9.46 I would like it to remain that way. The problem is I have many fields and don't want to change the properties of each of them one by one.

Thank you for the help.
Lenny

[rpeare]

so it's the properties on BOTH your form AND your TABLE?

[Leonidsg]

Yes that is correct.

[rpeare]

This code will change any field that's currently integer or long integer to double in your table you just have to substitute your table name where you see "table1".

BEWARE, if you have an autonumber field as your PK this will change the datatype to double. You will have to program an exception into your code if you do have an autonumber PK field.

Dim tdf As TableDef
Dim fld As Field
Dim db As Database
Dim sSQL As String
dim sTableName as string


Set db = CurrentDb
sTableName = "Table1"
Set tdf = db.TableDefs(stablename)

For Each fld In tdf.Fields
    Debug.Print fld.Name & "  " & fld.Type
    
    If fld.Type = 4 Or fld.Type = 10 Then
        sSQL = "ALTER TABLE " & stablename & " ALTER COLUMN " & fld.Name & " double"
        db.Execute sSQL
    End If

Next fld
set db = nothing

As far as changing the formats on a from, just select all the relevant fields and change them to general number through the properties box. You should be able to do all of them in one shot after you've selected them.

[Leonidsg]

I'm sorry for my stupidity, would you mind telling me where this code is inserted. Thank you.

[rpeare]

just create a module and paste the code into the module (macros and modules I think are part of the same ribbon option in access 2010 but I'm not 100% sure)