Replace problem

[myspiral]

I need to replace all instances of Ave with Avenue, which I can do except for when it occurs at the end of the field.
If you do a simple replace(txt,'Ave','Avenue') it screws up Avenue ie AveAvenue. Any advice on how to accomplish this?

 

[Remou]

How about:
[tt]Right(Trim(strStreet), 3) = "Ave"[/tt]

 

[myspiral]

how would I use this to replace though?

 

[Remou]

With a query, perhaps:
[tt]UPDATE tblCustomers SET tblCustomers.Street = Left([Street],Len([Street])-3) & " Avenue"
WHERE ((Right(Trim([Street]),3)="Ave"));[/tt]

 

[Golom]

There's always this

Replace(Replace(Replace(txt,"Avenue",Chr(6)),"Ave","Avenue"),Chr(6),"Avenue")

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[myspiral]

thanks guys both of those will work

 

[PHV]

A simpler way:
UPDATE ...
SET txt=replace(txt,'Ave','Avenue')
[!]WHERE txt Not Like '*Avenue*'[/!]

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

 

[Remou]

I am missing a point (as usual) Cave Street? Averna Road?

 

[Golom]

Remou

True enough

- Haversham Road
- Aveneda Drive
- AverageGuy Street

Needs a bit more thought to handle that stuff. Maybe

Replace(Replace(Replace(txt & " ","Avenue",Chr(6)),
        " Ave "," Avenue "),Chr(6),"Avenue")

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Remou]

So then Ave at the end of the line = " Ave"?

 

[Golom]

No ... notice that I concatenated a " " to the end of the text (i.e. txt & " ") to deal with that.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

UPDATE ...
SET txt = Replace(txt,'Ave','Avenue')
WHERE ' ' & txt & ' ' Like '* Ave *'

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