Re: Find and Replace against set of rules in 2nd table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You need to use the same kind of Join so the query
understands what Tbl3 is:

DELETE Tbl1.*
FROM Tbl1 INNER JOIN Tbl3
ON Tbl1.Col1 Like "*" & Tbl3.Col1 & "*"

***REPEAT PREVIOUS WARNING***
--
Marsh
MVP [MS Access]


Hank Rouse wrote:
>This solution worked PERFECTLY!!!
>
>Now I want to perform the following query:
>
>Delete TBL1 Record if TBL1,COL1 contains any partial text match from
>TBL3,COL1
>
>DELETE FROM Tbl1
>WHERE Tbl1.Col1 Like "*" & Tbl3.Col1
>
>It keeps prompting me to enter a parameter. The Parameters are the contents
>of Tbl3.Col1.
>
>What am I missing?
>
>"Marshall Barton" wrote
>> You can use an update query something like:
>>
>> UPDATE Tbl1 INNER JOIN Tbl2
>> ON Tbl1.Col1 Like "*" & Tbl2.Col2 & "*"
>> SET Tbl1.Col1 = Replace(Tbl1.Col1, Tbl2.Col2, Tbl2.Col1)
>>
>> WARNING - This may change more that you intend if you are
>> not very careful about what you use in Tbl2.Col1. Be sure
>> to test this on a copy of Tbl1 so you don't garble any real
>> data unitl you're sure it's doing exactly what you want.
>>
>>
>> Hank Rouse wrote:
>>
>> >TBL1
>> >
>> >COL1
>> >Mari Johnson
>> >Jim Evans
>> >Charles Stanley
>> >Jerry Straight
>> >
>> >TBL2
>> >
>> >COL1 COL2
>> >Mari Mary
>> >Straight Strait
>> >
>> >See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
>> >If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2
>> >
>> >
>> >"Hank Rouse" <hank_top_sf@xxxxxxxxxxx> wrote
>> >> Looking for a tool, or code that does the following.
>> >>
>> >> Edit [partial field data] in TBL1, if [partial field data] meets
>criteria
>> >in
>> >> TBL2, COL1. TBL2, COL2 has the REPLACE WITH information.
>> >>
>> >> Not sure if this edited data can be written directly to the TBL1, or
>needs
>> >> to write to a TEMP TBL. (Would prefer an Update Query to update TBL1)
>> >>
>> >> As I will continue to be tweaking and TBL2 COL1 & COL2, I would like
>this
>> >to
>> >> be a query so that I can run it as needed.
>

.