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

Marshall,

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" <marshbarton@xxxxxxxxxx> wrote in message
news:j8fdr1dk6mtbgkonhqcng6l10n85dgo9j6@xxxxxxxxxx
> 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.
> --
> Marsh
> MVP [MS Access]
>
>
> 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.


.

Relevant Pages

  • Re: Combine Tables
    ... Tbl1 is single field: ... Tbl2 has 2 fields: ... that determines where, the dash is. ... THEN use the query and tbl2 to create another query that joins ...
    (comp.databases.ms-access)
  • Re: Link table alternative
    ... If your query is an actual query & not an example: ... SELECT phsmgr, phscde, SUMAs Total ... The subquery in the SELECT clause will return a record for each phscde ... in tbl1, ...
    (microsoft.public.access.queries)
  • Re: Find and Replace against set of rules in 2nd table
    ... is a table of instructions for updating each Record of TBL1? ... > be a query so that I can run it as needed. ...
    (comp.databases.ms-access)
  • Re: Deleting Unmatched records
    ... I have created an unmatching query, using the wizard, on tbl2 with ... comparison to tbl1 to filter out which records in tbl1 no longer ... SQL View of query: ... PRIMARY KEY (Key1) ...
    (microsoft.public.access.queries)
  • Re: Find and Replace against set of rules in 2nd table
    ... UPDATE Tbl1 INNER JOIN Tbl2 ... >Jerry Straight ... (Would prefer an Update Query to update TBL1) ...
    (microsoft.public.access.queries)