Find/Replace Limitations? 1

[leadpro]

I'm trying to do a simple find a space and replace the space with nothing. Specifially a phone number from:

555 5555555
to
5555555555

I'm able to do this in the table, but it will only process a few thousand records at a time. I have 2.4 million phone numbers and will take forever! Please help with any suggestions.

Thanks!

LeadPro

 

[PHV]

Have you tried an Update query (need recent version of access) ?
UPDATE yourTable
SET [phone number]=Replace([phone number],' ','')
WHERE [phone number] Like '* *'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Alfredomulet]

PHV, which version of Access do you need to use the Replace function?
Can a similar function be used with Access 2000?
Cheers,

Alfredo

 

[jebry]

Hi LeadPro!

You can also write your own replace function, if necessary, as follows:

Public Function RemoveDash(StudentID As String) As String
Dim DashPos As Long

DashPos = InStr(StudentID, "-")
Do While DashPos <> 0
StudentID = Left(StudentID, DashPos - 1) & Mid(StudentID, DashPos + 1)
DashPos = InStr(StudentID, "-")
Loop
RemoveDash = StudentID

End Function

As you can see this is designed to remove dashes from a SSN but you can adjust it to your needs. You can even make it a general find and replace function by passing the character to be replaced and the character to replace it with.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[PHV]

Alfredomulet, in ac2k or above, create a function in a standard code module:

Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
  myReplace = Replace(myString, myFind, myRepl)
End If
End Function

And then your query:
UPDATE yourTable
SET [phone number]=myReplace([phone number],' ','')
WHERE [phone number] Like '* *';



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Alfredomulet]

Thanks PHV! as usual it worked great!
Alfredo