Auto fix phone numbers in field via update query

Access 2010...
I have a phone number field: formatted as (text) ,
that keeps getting jumbled with many variations of entries.
6XX.581.9998
8XX-3XX-XX74
(206)XXX-9698
2XX/ XXX-78XX

One exception: on international phone calls:
(XXX) 6980-XXXX   this is correct


final result should be:
(XXX)<space>2XX-XXX1
OR
for International numbers
(XXX)<space>X287-XXXXXX1

VBA code is great if can be supplied.



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not really a solution, ...But going forward, you might want to  consider using a phone Number "input mask' to this field.
This will force users to enter the data in the correct; order/format.

Now you can wait for an Expert o post a solution to "Fix" the existing data.
Eric ShermanAccountant/DeveloperCommented:
In addition to Jeffery Coachman's comment ... I would store the international phone numbers in a separate field if possible with input mask also.

ET
Eric ShermanAccountant/DeveloperCommented:
For starters, I would use a function like this one to strip out all non numeric characters from you existing field into a new field with the proper input mask.

Function GetOnlyDigits(s As String) As String
    
    Dim retval As String
    Dim i As Integer

    retval = ""

    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    GetOnlyDigits = retval

End Function

Open in new window


ET
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
fordraiders,
You can disregard my posts for any points consieration.
I am sure etsherman, can help you through to conclusion.
;-)

Jeff
Helen FeddemaCommented:
I have done a lot of fixing phone numbers over the years.  Here is some code from a database I can't post because of confidentiality (maybe more than you want, but you can probably modify it for your needs).  The 2-letter country codes are from a list of countries and their codes I got from the Internet.  Cities are also noted, since in some cases they have their own phone number formats (particularly in the UK).

Public Function FormattedPhoneNo(strRawPhoneNo As String, _
   strInternetCode As String, strCountryCode As String, _
      strCity As String, blnCell As Boolean) As String
'Created by Helen Feddema 23-Apr-2011
'Last modified by Helen Feddema 11-Sep-2011

On Error GoTo ErrorHandler

   Dim strNumberPortion As String
   Dim strExtPortion As String
   Dim intxPosition As Integer
   
   intLength = Len(strRawPhoneNo)
   strNumberPortion = Left(strRawPhoneNo, 10)
   intxPosition = Nz(InStr(strRawPhoneNo, "x"))
   
   If intxPosition > 0 Then
      strExtPortion = Mid(strRawPhoneNo, intxPosition)
   End If
   
   Select Case strInternetCode
   
      Case "US"
         strTitle = "Format problem"
         strPrompt = "United States phone numbers must have 10 digits"
      
         Select Case intLength
         
            Case 10
               FormattedPhoneNo = Format(strRawPhoneNo, "(###) ###-####")
            
            Case Is < 10
               MsgBox strPrompt, vbExclamation, strTitle
         
            Case Is > 10
               If intxPosition > 0 Then
                  FormattedPhoneNo = Format(strNumberPortion, "(###) ###-####") _
                     & " " & Trim(strExtPortion)
               Else
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
         
         End Select
      
      Case "CA"
         strTitle = "Format problem"
         strPrompt = "Canadian phone numbers must have 10 digits"
         
         Select Case intLength
         
            Case 10
               FormattedPhoneNo = Format(strRawPhoneNo, "(###) ###-####")
            
            Case Is < 10
               MsgBox strPrompt, vbExclamation, strTitle
         
            Case Is > 10
               If intxPosition > 0 Then
                  FormattedPhoneNo = Format(strNumberPortion, "(###) ###-####") _
                     & " " & Trim(strExtPortion)
               Else
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
         
         End Select
      
      Case "UK"
         'Because of the great complexity of UK phone formatting, not all numbers may
         'be formatted correctly
         strTitle = "Format problem"
       
         Select Case strCity
         
            Case "Aberdeen", "Chester", "Dundee", "Hartlepool", "Hull", "Luton", _
               "Morpeth", "Petersfield", "Penzance", "Preston", "Ullapool", "Whitby"
               strPrompt = "UK phone numbers for " & strCity & " must have 11 digits"
               
               Select Case intLength
               
                  Case 11
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(#####) ######")
               
                  Case Is > 11
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(#####) ######") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
         
            Case "Bolton", "Redditch", "Selkirk", "Workington", "Whitehaven"
               strPrompt = "UK phone numbers for " & strCity & " must have 10 digits"
               
               Select Case intLength
               
                  Case 10
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(#####) #####")
               
                  Case Is > 10
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(#####) #####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
                  
            Case "Birmingham", "Edinburgh", "Glasgow", "Liverpool", "Manchester", _
               "Leeds", "Sheffield", "Nottingham", "Leicester", "Bristol", "Reading"
               strPrompt = "UK phone numbers for " & strCity & " must have 11 digits"
                        
               Select Case intLength
               
                  Case 11
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(####) ###-####")
               
                  Case Is > 11
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(####) ###-####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
                                 
            Case "London", "Southampton", "Portsmouth", "Coventry", "Ballymena", _
               "Ballymena", "Larne", "Armagh", "Derry", "Omagh", "Belfast", "Lisburn", _
               "Cardiff"
               strPrompt = "UK phone numbers for " & strCity & " must have 11 digits"
            
               Select Case intLength
               
                  Case 11
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(###) ####-####")
               
                  Case Is > 11
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(###) ####-####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
         
            Case "Langholm", "Hornby", "Hawkshead", "Grange-over-Sands", "Sedbergh", _
               "Wigton", "Raughton Head", "Appleby", "Pooley Bridge", "Keswick", _
               "Gosforth", "", "", "", ""
               strPrompt = "UK phone numbers for " & strCity & " must have 11 digits"
               
               Select Case intLength
               
                  Case 11
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(######) #####")
               
                  Case Is > 11
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(######) #####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
         
               
            Case "Brampton"
               strPrompt = "UK phone numbers for " & strCity & " must have 10 digits"
         
               Select Case intLength
               
                  Case 10
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(######) ####")
               
                  Case Is > 10
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(######) ####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
            
            Case Else
               strPrompt = "UK phone numbers for " & strCity & " must have 11 digits"
                        
               Select Case intLength
               
                  Case 11
                     FormattedPhoneNo = "+" & strCountryCode & " " _
                        & Format(strRawPhoneNo, "(###) ####-####")
               
                  Case Is > 11
                     If intxPosition > 0 Then
                        FormattedPhoneNo = Format(strNumberPortion, "(###) ####-####") _
                           & " " & Trim(strExtPortion)
                     Else
                        MsgBox strPrompt, vbExclamation, strTitle
                     End If
                 
                  Case Else
                     MsgBox strPrompt, vbExclamation, strTitle
               
               End Select
         
         End Select
      
      Case "AU"
         strTitle = "Format problem"
         strPrompt = "Australian phone numbers must have 10 digits"
         
         Select Case intLength
         
            Case 10
               If blnCell = True Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strRawPhoneNo, "####-###-###")
               ElseIf blnCell = False Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strRawPhoneNo, "(##) ####-####")
               End If
         
            Case Is < 10
               MsgBox strPrompt, vbExclamation, strTitle
         
            Case Is > 10
               If intxPosition > 0 Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strNumberPortion, "(##) ####-####") _
                     & " " & Trim(strExtPortion)
               Else
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
         End Select
         
      Case "ZA"
         strTitle = "Format problem"
         strPrompt = "South African phone numbers must have 10 digits"
         
         Select Case intLength
         
            Case 10
               FormattedPhoneNo = Format(strRawPhoneNo, "(###) ###-####")
            
            Case Is < 10
               MsgBox strPrompt, vbExclamation, strTitle
         
            Case Is > 10
               If intxPosition > 0 Then
                  FormattedPhoneNo = Format(strNumberPortion, "(###) ###-####") _
                     & " " & Trim(strExtPortion)
               Else
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
         
         End Select
      
      Case "NZ"
         strTitle = "Format problem"
         strPrompt = "New Zealand phone numbers must have from 9 to 11 digits"
         
         Select Case intLength
         
            Case 9
               If blnCell = False Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strRawPhoneNo, "(##) ###-####")
               ElseIf blnCell = True Then
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
         
            Case 10
               If blnCell = True Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strRawPhoneNo, "(###) ###-####")
               ElseIf blnCell = False Then
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
            
            Case 11
               If blnCell = True Then
                  FormattedPhoneNo = "+" & strCountryCode & " " _
                     & Format(strRawPhoneNo, "(####) ###-####")
               ElseIf blnCell = False Then
                  MsgBox strPrompt, vbExclamation, strTitle
               End If
            
            Case Else
               MsgBox strPrompt, vbExclamation, strTitle
         
         End Select
         
   End Select

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FormattedPhoneNo procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Public Function FormattedStateProvince(strRawStateProvince As String, _
   strInternetCode As String) As String
'Created by Helen Feddema 9-Sep-2011
'Last modified by Helen Feddema 11-Sep-2011

On Error GoTo ErrorHandler

   Dim strRecordSource As String
   Dim strSQL As String
   Dim strQuery As String
   Dim lngCount As Long
   
   intLength = Len(strRawStateProvince)
   strQuery = "qryTemp"
   
   Select Case strInternetCode
   
      Case "US"
         If intLength = 2 Then
            strRecordSource = "tlkpStates"
            strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
               & "[StateCode] = " & Chr$(39) & UCase(strRawStateProvince) & Chr$(39) & ";"
            Debug.Print "SQL for " & strQuery & ": " & strSQL
            lngCount = CreateAndTestQuery(strQuery, strSQL)
            Debug.Print "No. of items found: " & lngCount
            If lngCount = 0 Then
               strPrompt = strRawStateProvince & " is not a valid State abbreviation; canceling"
               strTitle = "Canceling"
               MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
               GoTo ErrorHandlerExit
            Else
               FormattedStateProvince = UCase(strRawStateProvince)
            End If
         Else
            strTitle = "Format problem"
            strPrompt = "U.S. state abbreviations must have 2 characters"
            MsgBox strPrompt, vbExclamation, strTitle
         End If
      
      Case "CA"
         If intLength = 2 Then
            strRecordSource = "tlkpProvinces"
            strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
               & "[ProvinceCode] = " & Chr$(39) & UCase(strRawStateProvince) & Chr$(39) & ";"
            Debug.Print "SQL for " & strQuery & ": " & strSQL
            lngCount = CreateAndTestQuery(strQuery, strSQL)
            Debug.Print "No. of items found: " & lngCount
            If lngCount = 0 Then
               strPrompt = strRawStateProvince & " is not a valid Province abbreviation; canceling"
               strTitle = "Canceling"
               MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
               GoTo ErrorHandlerExit
            Else
               FormattedStateProvince = UCase(strRawStateProvince)
            End If
         Else
            strTitle = "Format problem"
            strPrompt = "Canadian state abbreviations must have 2 characters"
            MsgBox strPrompt, vbExclamation, strTitle
         End If
      
      Case Else
         'Do not format state or province
      
   End Select

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FormattedStateProvince procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Public Function FormattedPostalCode(strRawPostalCode As String, _
   strInternetCode As String)
'Created by Helen Feddema 23-Apr-2011
'Last modified by Helen Feddema 11-Sep-2011

On Error GoTo ErrorHandler

   strTitle = "Format problem"
    
   intLength = Len(strRawPostalCode)
   
   Select Case strInternetCode
   
      Case "US"
         Select Case intLength
         
            Case 5
               If strRawPostalCode Like "#####" Then
                  'Formatted correctly
                  FormattedPostalCode = strRawPostalCode
                  GoTo ErrorHandlerExit
               End If
               
            Case 9
               If strRawPostalCode Like "#########" Then
                  'Just needs a dash in the middle
                  FormattedPostalCode = Mid(strRawPostalCode, 1, 5) & "-" _
                     & Mid(strRawPostalCode, 6, 4)
               End If
            
            Case 10
               If strRawPostalCode Like "#####-####" Then
                  'Formatted correctly
                  FormattedPostalCode = strRawPostalCode
                  GoTo ErrorHandlerExit
               End If
         
            Case Else
               strPrompt = "United States Zip codes must have 5 or 9 digits"
               MsgBox strPrompt, vbExclamation, strTitle
               GoTo ErrorHandlerExit
         
         End Select
      
      Case "CA"
       strPrompt = "Canadian Postal codes must have 6 characters separated by a space"
      
       Select Case intLength
       
          Case 6
             If LCase(strRawPostalCode) Like "[a-z][0-9][a-z][0-9][a-z][0-9]" Then
                'Just needs a space in the middle
                FormattedPostalCode = UCase(Mid(strRawPostalCode, 1, 3) & " " _
                   & Mid(strRawPostalCode, 4, 3))
             Else
                MsgBox strPrompt, vbExclamation, strTitle
                GoTo ErrorHandlerExit
             End If
          
          Case 7
             If LCase(strRawPostalCode) Like "[a-z][0-9][a-z] [0-9][a-z][0-9]" Then
                'Formatted correctly
                FormattedPostalCode = UCase(strRawPostalCode)
                GoTo ErrorHandlerExit
             Else
                MsgBox strPrompt, vbExclamation, strTitle
                GoTo ErrorHandlerExit
             End If
          
          Case Else
             MsgBox strPrompt, vbExclamation, strTitle
             GoTo ErrorHandlerExit
       
       End Select
      
      Case Else
         'Do not format postal code
      
   End Select
      
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FormattedPostalCode procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window


To get the raw phone number, use this line of code on a textbox:

         strRawPhone = StripNonAlphaNumericChars(Nz(txt.Value))

Public Function StripNonAlphaNumericChars(strText As String) As String
'Strips a variety of non-alphanumeric characters from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

   Dim strTestString As String
   Dim strBadChar As String
   Dim i As Integer
   Dim strStripChars As String

   strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?" & Chr$(34) & Chr$(13) & Chr$(10)
   strTestString = strText

   For i = 1 To Len(strStripChars)
      strBadChar = Mid(strStripChars, i, 1)
      strTestString = Replace(strTestString, strBadChar, vbNullString)
   Next

   StripNonAlphaNumericChars = strTestString
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in StripNonAlphaNumericChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks very much !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.