Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



Phone Number Best Practices - Microsoft Access

Modified on Tue, Feb 15, 2011, 10:58 AM by Administrator Categorized as Microsoft Office

Field Definition

  • When creating a field that is to contain a phone number, use the input mask !\(999") "000\-0000;0;_. The zero (between the two semi-colons) indicates that the formatting characters (i.e., the parentheses and dash) are to be stored in the field data. This is key to getting phone numbers to format properly on reports.

  • Use this input mask on all forms as well.

Cleaning Existing Data

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), "")
    imin = Asc("9") + 1
    For i = imin To 255
        result = Replace$(result, Chr$(i), "")
    result = Format$(result, "(@@@) @@@-@@@@")
    FormatPhoneNumber = result

End Function

ScrewTurn Wiki version Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.