Option Compare Database
Option Explicit
'==================================================================================================
Public Sub CleanPhoneNumbers()

    'TODO: Add your own table/field combinations here
    CleanSinglePhoneNumber "Customers", "Phone"
    CleanSinglePhoneNumber "Customers", "Fax"
    
    MsgBox "Done cleaning phone numbers"
    
End Sub
'==================================================================================================
Private Sub CleanSinglePhoneNumber(tableName As String, fieldName As String)

    Dim sql As String
    Debug.Print "Cleaning phone numbers: [" + tableName + "].[" + fieldName + "]"

    sql = "UPDATE [" & tableName & "] SET [" & fieldName & "] = FormatPhoneNumber([" & _
            fieldName & "]) where [" & fieldName & "] Is Not Null"

    DbEngine.Workspaces(0).Databases(0).Execute sql

End Sub
'==================================================================================================
Public Function FormatPhoneNumber(data As String) As String

    Dim i As Integer
    Dim imin As Integer
    Dim imax As Integer
    Dim result As String
    
    result = data & ""
    
    imax = Asc("0") - 1
    For i = 0 To imax
        result = Replace$(result, Chr$(i), "")
    Next
    
    imin = Asc("9") + 1
    For i = imin To 255
        result = Replace$(result, Chr$(i), "")
    Next
    
    result = Format$(result, "(@@@) @@@-@@@@")
    
    FormatPhoneNumber = result

End Function