Change field format in table

[adam.greer]

Hi all

I'm trying to change the 'Format' of a number field in a table. I've already converted the field from Text to Number with the following code

db.Execute "ALTER TABLE tmpClientSatisfactionExport ALTER COLUMN Satisfied NUMBER;"

This works great, however I also want to change the format to 'Percent'. I'm not sure if it's possible with this function. I've also tried the following code

    Dim DB As Database
    Dim TD As DAO.TableDef
    Dim fld As DAO.Field
    Dim prop As DAO.Property
    Dim strFormat As String
    Dim strFormat2 As String

    
    Set DB = CurrentDb
    Set TD = DB.TableDefs("tmpClientSatisfactionExport")
    Set fld = TD.Fields("Satisfied")

    fld.Properties("Format") = "Percent"

That returns with the error 'Property Not Found'. I have included DAO 3.6 in my references.

Any help would be much appreciated.

 

[ByteMyzer]

You might try the following revision to your code:

[COLOR="Navy"]On Error Resume Next[/COLOR]
fld.Properties("Format") = "Percent"
[COLOR="navy"]If[/COLOR] Err.Number = 3270 [COLOR="navy"]Then[/COLOR] [COLOR="DarkGreen"]' Property not found[/COLOR]
    Err.Clear
    [COLOR="navy"]Set[/COLOR] prop = fld.CreateProperty("Format", dbText, "Percent")
    fld.Properties.Append prop
    [COLOR="navy"]Set[/COLOR] prop = [COLOR="navy"]Nothing
End If
On Error GoTo 0[/COLOR]
fld.Properties.Refresh
td.Fields.Refresh

 

[adam.greer]

That worked perfectly.

Thanks very much!

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you.