Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Phone Number Best Practices - Microsoft Access

RSS
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

{copytext|vb}
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-2018, Patrick Jasinski.