Link to home
Start Free TrialLog in
Avatar of Molly152
Molly152Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

For one thing, you need to define

RemoveSpaces

Dim RemoveSpaces As String
Also

strSQL  

and

i  

in

 For i = 0 To rst.Fields.Count - 1
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
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
btw ... could this be simplified?

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

returns 909-333-5555   for example

?

mx
Avatar of Molly152

ASKER

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?
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.
"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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This will remove parens, spaces and dashes:

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

mx
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Leigh ... I tried the RegEx ... COOL!

mx
Thanks everyone ...  I can now sleep!