Remove spaces () * and text from telephone numbers

I have beem given code to remove spaces, () * and text from ContactTelephone and update CleanTel with the clean number.  Unfortunately I keep getting the error message "Undefined function'removealphas' in expression.  I have been trying for several hours to make it work, but being a novice my head is just about to explode!

Please help

Sub CleanTEMPTable()

    Dim AlphaNum As Variant
    Dim Clean As String
    Dim Pos, A_Char$
    Dim removealphas As String

   Pos = 1
   If IsNull(AlphaNum) Then Exit Sub

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   removealphas = Clean$


   Pos = 1
   If IsNull(AlphaNum) Then Exit Sub

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If (A_Char$ >= "A" And A_Char$ <= "Z") Or (A_Char$ >= "0" And A_Char$ <= "9") Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveSpaces = Clean$


   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim already As Boolean

   already = False

   Set db = CurrentDb()
   strSQL = "SELECT * FROM tblTEMPTable"
   Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

   For i = 0 To rst.Fields.Count - 1
      If rst.Fields(i).Name = "CleanTel" Then already = True
   Next i

   rst.Close
   Set rst = Nothing
   db.Close
   Set db = Nothing

   If Not already Then
      strSQL = "ALTER TABLE tblTEMPTable ADD CleanTel varchar(50)"
      DoCmd.RunSQL (strSQL)
   End If


   strSQL = "UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = removealphas([ContactTelephone]) " _
          & "WHERE (((tblTEMPTable.CleanTel) Is Null) AND " _
          & "((tblTEMPTable.ContactTelephone) Is Not Null));"
         
   DoCmd.SetWarnings False
   DoCmd.RunSQL (strSQL)
   DoCmd.SetWarnings True

End Sub
Molly152Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
place this codes in a module

Function GetNumbers(strIn As String) As String
Dim j, numOnly
If strIn = "" Then Exit Function
For j = 1 To Len(strIn)
    If IsNumeric(mid(strIn, j, 1)) Then
        numOnly = numOnly & mid(strIn, j, 1)
    End If
Next
GetNumbers = numOnly
End Function


in a click of a button

private sub cmdUpdate_click()
dim strSQL as string

strSQL = "UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = GetNumbers([ContactTelephone]) " _
          & "WHERE (((tblTEMPTable.CleanTel) Is Null) AND " _
          & "((tblTEMPTable.ContactTelephone) Is Not Null));"

currentdb.execute strsql,dbfailonerror

end sub



0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For one thing, you need to define

RemoveSpaces

Dim RemoveSpaces As String
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also

strSQL  

and

i  

in

 For i = 0 To rst.Fields.Count - 1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You need to set

Option Explicit

in the Declarations section of the module this code is in, the

Menu>>Debug>>Compile

this will expose the various missing declarations ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So ... in summary so far ... add these:

    Dim RemoveSpaces As String
    Dim strSQL As String
    Dim i As Long

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Now, it would appear the actual problem is here:

"UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = removealphas([ContactTelephone])

removealphas([ContactTelephone])

To Access, that looks like a Function call, but ... there is no removealphas function ??

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
btw ... could this be simplified?

Replace(Replace("(909)-333-5555", "(", ""), ")", "")

returns 909-333-5555   for example

?

mx
0
 
Molly152Author Commented:
Thanks for your comments, I know that you are right with the SQL problem, but I do not know how to mend the text to make it work.  Anyway to simplify this would be great, I really want a column with only numbers, no dashes, spaces etc.  Can thhis be done?
0
 
Leigh PurvisDatabase DeveloperCommented:
There really are *many* ways to do what you want.
One example of a function to give you a numeric expression only could be

Function fGetNumOnly(strText As String) As String
   
    Static objRegEx As Object
   
    If objRegEx Is Nothing Then
        Set objRegEx = CreateObject("VBScript.RegExp")
    End If
   
    With objRegEx
        .Pattern = "\D"
        .Global = True
        .IgnoreCase = True
        fGetNumOnly = .Replace(strText, "")
    End With
   
End Function

(Uses a slight cheat to expidite the process - could be cleaner).

Then you'd execute your SQL statement.
UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = fGetNumOnly([ContactTelephone])

There are other ways of course of parsing strings and removing elements.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I know that you are right with the SQL problem, "

But, where IS that function ?

Could you give some examples of the data (ph numbers) also?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This will remove parens, spaces and dashes:

Replace( Replace( Replace("(858)-395-999","(","")  ,")","") ,"-","")

mx
0
 
Molly152Author Commented:
Hi Capricorn1

Thanks for your solution, I have set the module and called it GetNumbers,

Function GetNumbers(strIn As String) As String
Dim j, numOnly
If strIn = "" Then Exit Function
For j = 1 To Len(strIn)
    If IsNumeric(Mid(strIn, j, 1)) Then
        numOnly = numOnly & Mid(strIn, j, 1)
    End If
Next
GetNumbers = numOnly
End Function

and I have made a click button and copied your text, but I still get an error message Underfined Function 'GetNumbers' in expression.  Can you tell me what I am doing wrong
0
 
Leigh PurvisDatabase DeveloperCommented:
Rename your module to something other than 'GetNumbers'
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Check to be sure you have a Reference set to Microsoft DAO 3.xx

vba editor >> Tools>>References ....

If this is a new mdb, ADO is the default data handling reference ....

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Leigh ... I tried the RegEx ... COOL!

mx
0
 
Molly152Author Commented:
Thanks everyone ...  I can now sleep!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.