normalizing phone number fields 1

[patrichek]

Hi,
I need advice as to how to format my phone number field. Right now its not normalized. I have (nnn) nnn-nnnn, nnnnnnnnnn, nnn-nnn-nnnn, +nn-nnn-nnnn and so on...
we get data from several different places and everyone seems to have a different way of inputting area codes and then throw in International numbers making it even more interesting.

The first part of my question is should I create a mask for that field in my table design (probably not),

2nd: how do I update my table to remove the ( ) from the area codes? I've tried in query design running an update query Replace([workphone],"(","") and receive an error.

Thanks in advance

 

[Golom]

Here's a little class that I use

Option Explicit

Private mvarAreaCode        As String
Private mvarNumber          As String
Private mvarParsedNumber    As String

Public Property Let Number(ByVal vData As String)

    mvarNumber = vData
    mvarParsedNumber = Trim$(vData)
    mvarParsedNumber = Replace(mvarParsedNumber, " ", "")    ' Remove spaces
    mvarParsedNumber = Replace(mvarParsedNumber, "-", "")    ' Remove dash
    mvarParsedNumber = Replace(mvarParsedNumber, "(", "")    ' Remove left paren
    mvarParsedNumber = Replace(mvarParsedNumber, ")", "")    ' Remove right paren

End Property

Public Property Get ValidPhoneNumber() As Boolean
    Dim n                           As Integer
    Dim c                           As String

    ValidPhoneNumber = False
    ' Len(Nums) = 7                               - Seven digit number
    ' Len(Nums) = 10                              - Area code + Number
    ' (Len(Nums) = 11 And Left$(Nums, 1) = "1")   - "1" preceding area code & number

    For n = 1 To Len(mvarParsedNumber)
        c = Mid$(mvarParsedNumber, n, 1)
        If c < "0" Or c > "9" Then
            ValidPhoneNumber = False
            Exit Property
        End If
    Next

    If Len(mvarParsedNumber) = 7 Or _
       Len(mvarParsedNumber) = 10 Or _
       (Len(mvarParsedNumber) = 11 And Left$(mvarParsedNumber, 1) = "1") Then
        ValidPhoneNumber = True
    End If

End Property

Public Property Get Number() As String

    If Not IsNumeric(mvarParsedNumber) Then
        Number = mvarNumber
    ElseIf Len(mvarParsedNumber) = 7 Then
        Number = Left$(mvarParsedNumber, 3) & "-" & Right$(mvarParsedNumber, 4)
    ElseIf Len(mvarParsedNumber) = 10 Then
        Number = Mid$(mvarParsedNumber, 4, 3) & "-" & Right$(mvarParsedNumber, 4)
    ElseIf Len(mvarParsedNumber) = 11 And Left$(mvarParsedNumber, 1) = "1" Then
        Number = Mid$(mvarParsedNumber, 5, 3) & "-" & Right$(mvarParsedNumber, 4)
    ElseIf Len(mvarParsedNumber) = 0 Then
        Number = ""
    Else
        Number = mvarNumber
    End If
    
End Property


Public Property Get FullNumber() As String
    If Len(Me.AreaCode) > 0 Then
        FullNumber = "(" & Me.AreaCode & ") " & Me.Number
    Else
        FullNumber = Me.Number
    End If
End Property

Public Property Get ParsedNumber() As String
    If Len(Me.Number) = 0 Then
        ParsedNumber = ""
    Else
        ParsedNumber = Me.AreaCode & Left$(Me.Number, 3) & Right(Me.Number, 4)
    End If
End Property



Public Property Let AreaCode(ByVal vData As String)
    mvarAreaCode = vData
End Property


Public Property Get AreaCode() As String

    AreaCode = mvarAreaCode
    
    If Len(mvarAreaCode) = 0 Then
        If Len(mvarParsedNumber) = 10 Then
            AreaCode = Left$(mvarParsedNumber, 3)
        ElseIf Len(mvarParsedNumber) = 11 And Left$(mvarParsedNumber, 1) = "1" Then
            AreaCode = Mid$(mvarParsedNumber, 2, 3)
        Else
            AreaCode = ""
        End If
    End If
    
End Property

If you set the number with something like

With New Phone
    .Number = "(800)5558526"
    Debug.Print .Number       [red]<-- 555-8526[/red]
    Debug.Print .FullNumber   [red]<-- (800) 555-8526[/red]
    Debug.Print .ParsedNumber [red]<-- 8005558526[/red]
    Debug.Print .AreaCode     [red]<-- 800[/red]
End With

You would probably need to add some processing to deal with international dialing codes.

 

[patrichek]

wow, thanks! its been a while since i did any functions. where do i put my field name?
thanks!

 

[Golom]

The purpose of this class is to allow you to take a phone number in some arbitrary format and have the class return it to you in some standardized form. If I were attempting to update phone numbers in a table I would write a public function.

Public Function RevPhone(PhoneNumber As String) As String
With New Phone
   .Number = PhoneNumber
   RevPhone = .FullNumber  [red]Produces (aaa) nnn-nnnn[/red]
   [blue]OR[/blue]
   RevPhone = .ParsedNumber [red]Produces aaannnnnnn[/red]
   [blue]OR[/blue]
   RevPhone = .Number       [red]Produces nnn-nnnn[/red]
End With
End Function

and then your SQL is

UPDATE myTable SET [Phone] = RevPhone([Phone])

 

[patrichek]

thanks again, this works great!