Search and Replace Query

[Freckles]

I need a query that will look for "-" anywhere in a number and delete them. Example: 8888-88-4444444 or 8888-88-4444444 8888884-44-44-44 to 8888884444444

I would really like to do this in a query if at all possible and I did not find anything that would work when I searched on replace.


: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[dhookom]

Did you look at the Replace() function in an update query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Usually the Replace function

UPDATE tbl SET fld = Replace ( fld, "-", "" )

 

[Freckles]

Yes, but I can not do it in a query. And I run a number of queries for this database don't want to change the game here at crunch time.

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Golom]

First Post: I need a query ...

Last Post: I can not do it in a query ...

I'm confused. What are you looking for exactly?

 

[PHV]

I can not do it in a query
If ac2k or above you may create this function callable from a query:
Public Function myReplace(myString, myFind As String, myRepl As String)
If Len(Trim(Nz(myString & ""))) > 0 Then
myReplace = Replace(myString, myFind, myRepl)
End If
End Function

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

 

[Freckles]

Golom -- sorry I was confusing. I meant that I could not figure out a way to get the replace function into a query. But I need a query because I can not restructure the world right here at crunch time.

PHV -- I will be investigating your methodology during my lunch hour and get back. Thanks



: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.