Molly152
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([ContactTelep hone]) " _
& "WHERE (((tblTEMPTable.CleanTel) Is Null) AND " _
& "((tblTEMPTable.ContactTel ephone) Is Not Null));"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub
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([ContactTelep
& "WHERE (((tblTEMPTable.CleanTel) Is Null) AND " _
& "((tblTEMPTable.ContactTel
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub
Also
strSQL
and
i
in
For i = 0 To rst.Fields.Count - 1
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Now, it would appear the actual problem is here:
"UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = removealphas([ContactTelep hone])
removealphas([ContactTelep hone])
To Access, that looks like a Function call, but ... there is no removealphas function ??
mx
"UPDATE tblTEMPTable SET tblTEMPTable.CleanTel = removealphas([ContactTelep
removealphas([ContactTelep
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
Replace(Replace("(909)-333
returns 909-333-5555 for example
?
mx
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.Reg Exp")
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([ContactTeleph one])
There are other ways of course of parsing strings and removing elements.
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.Reg
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([ContactTeleph
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
But, where IS that function ?
Could you give some examples of the data (ph numbers) also?
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This will remove parens, spaces and dashes:
Replace( Replace( Replace("(858)-395-999","( ","") ,")","") ,"-","")
mx
Replace( Replace( Replace("(858)-395-999","(
mx
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
mx
ASKER
Thanks everyone ... I can now sleep!
RemoveSpaces
Dim RemoveSpaces As String