Keyword Lookup/Find & Update

[air1access]

I have a tbl with a fld named "ReasonForRemoval" (Memo d/Type). The values in the field are things like:
"CHAFFED OUT OF LIMITS. NOT PART MARKED"
"AS PER EO MD11-2840-22551"
"AS PER TC 27-075-51" - etc...

I have another fld in the tbl named "RemovalType" which consists of values like "UNS" or "SCH".
The tbl is populated with data from our MainFrame system, so the data is dirty, meaning people don't enter "UNS" (if the part was removed for unscheduled reasons) or "SCH" (if the part was removed for scheduled reasons) the right way. The "ReasonForRemoval" fld shows why the part was removed.

I have gone thru the tbl and picked out "keywords" that would be used to update fld "RemovalType" to the value "SCH"... So far I am up to 52 different "keywords" and counting. I was going to build an "IIF Statement" with a wildcard function (Like "*TC*") to update the "RemovalType" fld... I'm thinking this is to ugly because of some many "keywords" & the amount of "IIF Statements" I would have to build...

Is there a way to build another tbl with only the "keywords" as values, and then run some code to update the "RemovalType" fld...?? Not sure how to handle mass "keyword" wildcard updates...

Any suggestions or examples would be the huckleberry..!!
Thanks in advance...!!!
air1access

 

[Remou]

You should be able to do something like:

UPDATE tblTable, tblKeywords SET FieldX="UNS " & FieldX WHERE FieldX Like "*" & Keyword & "*"