?
Solved

Remove spaces () * and text from telephone numbers

Posted on 2007-07-21
16
Medium Priority
?
573 Views
Last Modified: 2008-05-19
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
0
Comment
Question by:Molly152
  • 10
  • 3
  • 2
  • +1
16 Comments
 
LVL 75
ID: 19540187
For one thing, you need to define

RemoveSpaces

Dim RemoveSpaces As String
0
 
LVL 75
ID: 19540194
Also

strSQL  

and

i  

in

 For i = 0 To rst.Fields.Count - 1
0
 
LVL 75
ID: 19540196
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 19540199
So ... in summary so far ... add these:

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

mx
0
 
LVL 75
ID: 19540215
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
 
LVL 75
ID: 19540253
btw ... could this be simplified?

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

returns 909-333-5555   for example

?

mx
0
 

Author Comment

by:Molly152
ID: 19540361
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 19540411
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
 
LVL 75
ID: 19540439
"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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 19540446
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
 
LVL 75
ID: 19540469
This will remove parens, spaces and dashes:

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

mx
0
 

Author Comment

by:Molly152
ID: 19540541
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
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 400 total points
ID: 19540552
Rename your module to something other than 'GetNumbers'
0
 
LVL 75
ID: 19540557
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
 
LVL 75
ID: 19540558
Leigh ... I tried the RegEx ... COOL!

mx
0
 

Author Comment

by:Molly152
ID: 19540608
Thanks everyone ...  I can now sleep!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question