!\(999") "000\-0000;0;_
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
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.