Find Replace multiple criteria

[stars14]

My database has a street address field that I would like find and replace a handful of certain strings with other strings. For example I would like to see:

Sunset Point = Sunset Pt
Main = Main
Prospect = Prospect
Caminito Paseo = Camto Paseo
Camino Litoral = Cam Litoral

I would like to perform something exactly like Find and Replace but to multiple strings in ONE function or query to update.

Is this possible? I looked at fReplace() but not sure where to put this.

Thank you.

STARS14

 

[ghudson]

If you are doing this then the easy way out would be just to use the built in Find & Replace function. Just open up the table and do the old Ctrl + H

You can only find and replace one text string at a time.

Two of your examples above [Main = Main & Prospect = Prospect] need no action. What are you trying to do?

 

[chambana1171]

Good Question

I would like to know how to do this too because the database I am working on contains tests and answers. If the student doesn't answer exactly to the key, then it is counted wrong. I would like to head them off at the path, by putting in some kind of code, that automatically changes incorrect entries and mispellings to the correct spelling, thus it doesn't affect their test score.

 

[stars14]

I am trying to Find and Replace on MORE THAN ONE STRING AT A TIME for those that need changed, so obviously not Main = Main, but Caminito --> Camto which occurs many times along with dozens of other changes.
I need this because my table is large and is will become part of a monthly routine when importing records into my table.

I guess I will have to look at other means since you say this is not possible.

I did find this tool, http://www.rickworld.com/download.html but have not tried it yet.

Thanks.

emkae

 

[ghudson]

I believe the Find and Replace add-in you have found will allow you to find and replace object names within a db. Kinda like what Speed Ferett will do but for a cheaper price [you get what you pay for.] I do not think that is what you want or need to replace text strings in a table.

If you know what you want to find and replace you could create a ton of SQL update queries or better yet get a find and replace SQL update to cycle through a listing of strings keyed into a special table. Good luck!

 

[Pat Hartman]

Actually the simplest method is to create a translation table. You need two columns - OldValue and NewValue. OldValue should be the primary key to prevent duplicates.

You then join to this table by joining the field you want to change to the OldValue field and updating the field you want to change to the NewValue.

No Code is required and only one query is required.

 

[stars14]

The street name text is only part of the string, so that is why Find and Replace was the method of choice. However, your suggestion has helped as I will have to go back and use this method on the street name field BEFORE I concatenate with other fields. Thanks

1 step forward and 2 steps back.

emkae