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