Replace Text in String - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Replace Text in String

Carlos Cavazos

6 years ago

Hi, find and replace a whole or part of a word is easy. Thanks to your videos. I bought the whole beginners series. And I jumped to Access_2013_Expert_14 and Access_2013_Expert_25.But I still can't find a solution to my issue. Here are some title examples in which I would like to remove some characters:
Have: Supervisor II, PSS 205  -------- Want: Supervisor II
Have: Custodian - PSS 2833  --------  Want: Custodian
Have: Support IT, - PSS II        --------  Want: Support IT
Have: Quality Control  PSS 75    --------Want: Quality Control
And yes I have hundreds records that I need to fix and this will be on a daily basis. So I need to lean something that will make my life easier.
What other video(s) do I need to buy to accomplish my goal?

Richard Rost @Reply

6 years ago

You're on the right track. Expert 14 and 25 cover the string functions you need. Now put those to work in an Update Query which is in Expert 13. If you only have a small number of strings you have to check for, just make an Update Query that looks for each of those strings and replaces the text accordingly.

IIF(Left(MyField,9)="Custodian","Custodian",MyField)

Use that as the criteria for your replacement. It says "if the left 9 characters of MyField equal Custodian, then set this field equal to Custodian, otherwise leave it alone."

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View