Delete Special characters

manu

Registered User.
Local time
Today, 15:41
Joined
Jun 28, 2001
Messages
51
My Phone field has values like (626) 123 5555. How can I delete the brackets?

All Help is appreciated
 
Use an update query that searches for "(" or ")" and don't replace them with anything.
 
One Possibility

The sql would be something like this:

UPDATE tblTest SET tblTest.Phone = fncEditPhoneNumbers([Phone])
WHERE (("1"="1"));


With the function like so:

Function fncEditPhoneNumbers(strPhonNumb As String) As String

Dim strNumberOnHold As String
Dim strSQL As String
Dim lLength As Long
Dim iCount As Integer
Dim strHold As String

strNumberOnHold = ""
lLength = Len(strPhonNumb)
iCount = 1
Do Until iCount > lLength
strHold = Mid(strPhonNumb, iCount, 1)
If strHold = "(" Or strHold = ")" Then
' Nothing
Else
strNumberOnHold = strNumberOnHold & strHold
End If
iCount = iCount + 1
Loop
fncEditPhoneNumbers = strNumberOnHold

End Function
 
Here's a reasonably quick function to remove unwanted characters from a string. You might test it against Fuzzygeek's fncEditPhoneNumbers() to see if there's any appreciable difference.
Code:
Function ChopIt(pStr As String, ParamArray VarMyVals() As Variant) As String
'*******************************************
'Name:      ChopIt (Function)
'Purpose:   Remove a list of unwanted
'           characters from a string
'Inputs:    From debug window:
'           '? chopit("(626) 123 5555", ")","(")
'Output:    626 123 5555
'*******************************************

Dim strHold As String
Dim i As Integer, n As Integer

strHold = Trim(pStr)
'check for entry
If UBound(VarMyVals) < 0 Then Exit Function
For n = 0 To UBound(VarMyVals())
   Do While InStr(strHold, VarMyVals(n)) > 0
      i = InStr(strHold, VarMyVals(n))
      strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
   Loop
Next n
ChopIt = Trim(strHold)
End Function
 
Hi dcx693,

Thanks for responding, however, could you pls help me construct your suggested query...

Thanks for your time in advance....

Regards,
 
manu, I worded my response rather concisely, so it might have made it sound like the easiest.

The other two who responded presented more general solutions. Let me ask you one thing: if the ( and ) symbols do occur will the ( symbol always be the first character? Meaning, could it occur somewhere else in the string? Likewise, will the ) always occur in the same location?

If it's highly unlikely these symbols could occur elsewhere in the string, then the query can be simpler. Just use the Left and the Mid functions to take the parts of the string you want should the ( and/or ) be found.

Write back and let us know.
 
Thanks dcx693,

This one seems to be a challenging one for me. I anticipate the ( or ) to occur at the same position in the string most of the time whereby could you pls send me the syntax for update query that I could run...

regards & thanks for your time.....
 
If the brakcet characters could occur anywhere in the string, then it's best to use a general solution like the ones proposed by fuzzygeek and raskew.

fuzzygeek, can't this syntax:
UPDATE tblTest SET tblTest.Phone = fncEditPhoneNumbers([Phone]) WHERE (("1"="1"));
simply be written without the WHERE clause?
 
Hi Fuzzygeek:

Could you pls take a moment and respond to dcx693's question.....I would really aprreciate having the update query syntax that can take care of the brackets....

Thanks for your time in advance....
 
Gentlemen,

Attached are some relevant screen shots to give you an idea of what I've done so far and the results.....

Looking forward to your help so I can resolve this matter ASAP...

Thanks a lot for yout patience & time

Regards,

Manu
 

Attachments

Using a copy of the Employees table (Employees1) imported from Northwind here's how you'd do it with the ChopIt() function mentioned above:
Code:
UPDATE Employees1 SET Employees1.HomePhone = chopit([HomePhone],")","(");
Give that a try and see if it's not what you're looking for.

Bob
 
Hi Raskew,

Attached are results based on your suggested method....pls advise next steps...

Regards,
 

Attachments

Manu-

OK, you got an 'undefined function' error because it couldn't find ChopIt(). Go up about 4 or 5 posts, find the function I provided, copy/paste it to a new module in your application, and try it again.

Bob
 
Hi Bob,

Thanks for your promptness & patience.....I know this is frustrating because running the Update query after copy/paste of the entire previously posted code to a new module still yields the same result "undefined function"

I hope this is not because of a silly oversight at my end....pls let me know...

Regards & Thanks.....
 
Try adding Public to your function:

Public Function ChopIt ....


Step by step:

In the DB window (F11) goto Modules
Click New
Paste the function
press CTRL + G
Now paste this: ? chopit("(626) 123 5555", ")","(")
It should return: 626 123 5555
Save

Now it should work (in the query as well)

Regards

The Mailman
 
Thanks Mailman,

The code did work in debug window [it removed the brackets] but the query still returns "undefined Function" error even after including the prefix of "Public" in the function.....

Any further suggestions?

Regards,
 
If it works in the debug window then it MUST also work in the query!!! This cant be!

Are you putting it in a module inside a form ?
You are sure you typed the function right?

Try:
New query
Dont add a table
Right click a field
choose build (you get a popup window -the expresion builder)
In the lower left window double click Functions
Now click The db name (below build in probably)
in the middle window click the module name you created
The function should now be visible in the outmost right window...

Regards
 
Thanks Mailman,

The code worked in debug window but the query still returns "undefined function" error even after adding the prefix of Public to the function.

Your time and patience is appreciated....Pls advise possible next steps.

Regards,
 
namliam said:
If it works in the debug window then it MUST also work in the query!!! This cant be!

Are you putting it in a module inside a form ?
You are sure you typed the function right?

Try:
New query
Dont add a table
Right click a field
choose build (you get a popup window -the expresion builder)
In the lower left window double click Functions
Now click The db name (below build in probably)
in the middle window click the module name you created
The function should now be visible in the outmost right window...

Regards
Did you try that?
 
Yes Mailman...

I've tried your suggestion....

Attached are some screenshots indicating the code that I'm working with and results.....

Regards,
 

Attachments

Users who are viewing this thread

Back
Top Bottom