Update Query to Remove Special Charaters 2

[perrymans]

I am importing data and have records where the () and - in a phone number were typed into the field:

(555) 555-2234

Is there a way to do an update query that removes the ()- characters (and spaces) from the field?

I have the formatting taken care of in the field of the form.

Thanks. Sean.

 

[RuralGuy]

You can either use nested Replace() functions or create your own function.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

Howdy perrymans . . .

In parallel with [blue]RuralGuy[/blue], try the following:
[ol][li]In a module in the [blue]modules window[/blue], copy/paste the following function

[blue]Public Function CorrectPhone(ByVal PhNum)
   PhNum = Replace(PhNum, "(", "")
   PhNum = Replace(PhNum, ")", "")
   PhNum = Replace(PhNum, "-", "")
   PhNum = Replace(PhNum, " ", "")
   CorrectPhone = PhNum
End Function[/blue]

[/li]
[li]In design view of the update query under your [blue]PhoneFieldName[/blue], set [blue]Updated To:[/blue] as follows:

[blue]CorrectPhone([[purple][B][I]YourPhoneFieldName[/I][/B][/purple]])[/blue]

[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

In a standard code module create the following function:

Public Function getPhoneNum(strPhone)
Dim i As Integer, x As String, S As String
If Trim(strPhone & "") = "" Then Exit Function
For i = 1 To Len(strPhone)
  x = Mid(strPhone, i, 1)
  If IsNumeric(x) Then S = S & x
Next
getPhoneNum = S
End Function

And now your update query:

UPDATE yourTable
SET [phone field] = getPhoneNum([phone field])
WHERE [phone field] Like '*[!0-9]*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[perrymans]

Thanks AceMan and PH.

PHV's solution worked better because of the nulls contained in some of the records and it alos got rid all all characters that weren't numbers.

Very nice.

Thanks. Sean.