Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Change field formats

    I have three tables in my database that have 30+ numeric fields. I want to change the formats to standard so when I view the tables or queries I see the comma separators.

    Is there a way, with VBA or otherwise, to change the format in all numeric fields in a table without having to go in and change them one by one?

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try something like:
    Code:
    Sub ChangeFormat()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            For Each fld In tdf.Fields
                If Eval(fld.Type & " IN (2, 3, 4, 5, 6, 7, 9, 11, 17, 19, 20, 21 )") = True Then ' Numeric data types only.
                    For Each pty In fld.Properties
                        If pty.Name = "Format" Then
                            pty.Value = "Standard"
                            fld.Properties.Refresh
                            Exit For
                        End If
                        If pty Is Nothing Then
                            Set pty = fld.CreateProperty("Format", dbText, "Standard")
                            fld.Properties.Append pty
                            Set pty = Nothing
                        End If
                    Next pty
                End If
            Next fld
        Next tdf
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I can't get Sinndho's hack to work, using v2007. But in Form Design View, you can change the Properties of multiple Controls, in one fell swoop, by
    • Pressing and holding down the <Shift> Key
    • Click on each desired Control to select it
    • Go to the Properties Pane and assign the value to the Property in question

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry ! Here's a corrected version (tested with Acc2003, Acc2007 and Acc2010):
    Code:
    Sub ChangeFormat()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If Left(tdf.Name, 4) <> "MSys" Then
                For Each fld In tdf.Fields
                    If Eval(fld.Type & " IN (2, 3, 4, 5, 6, 7, 9, 11, 17, 19, 20, 21 )") = True Then ' Numeric data types only.
                        For Each pty In fld.Properties
                            If pty.Name = "Format" Then
                                pty.Value = "Standard"
                                fld.Properties.Refresh
                                Exit For
                            End If
                        Next pty
                        If pty Is Nothing Then
                            Set pty = fld.CreateProperty("Format", dbText, "Standard")
                            fld.Properties.Append pty
                            Set pty = Nothing
                        End If
                    End If
                Next fld
            End If
        Next tdf
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •