Compare 2 field values with LIKE

  • Thread starter Thread starter Ulrich Sommer
  • Start date Start date
U

Ulrich Sommer

Hi,

I want to compare 2 field values with NOT LIKE:

Table 1 contains fields with strings like

Adobe Acrobat
Adobe Acrobat Reader
Quick Time
Quick Time Editor

Table 2 contains the key worrds that I want to suppress
like

Reader
Player

The resulting query should only show

Adobe Acrobat
Quick Time

Since the key word list will be several 100 words long, I
do not want to include them into the query as strings like

NOT LIKE *Reader* OR NOT LIKE *Player*.

I tried stuff like

NOT LIKE "*"&[Table2].[keyword]&"*"

but that does not work.

Any idea, even totally different, how to get to the
result?

Thanks,


Ulrich
 
You could try the following UNTESTED variations. They should all work, but
one may be faster than the others. Make sure that Table2.FieldMatch does
not contain a null value or a zero length string (zls) or you will have no
results returned since the null/zls field will end up creating a non-match
for every record in table1.

SELECT Table1.*
FROM Table1 Left Join Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*"
WHERE Table2.FieldMatch is Null


Or

SELECT Table1.*
FROM Table1
WHERE Table1.FieldMatch In
(SELECT Table1.FieldMatch
FROM Table1 INNER JOIN Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*")

Or

SELECT Table1.*
FROM Table1, Table2
WHERE Table1.FieldMatch Not Like "*" & Table2.FieldMatch & "*"
 
Hello John,

thanks for your reply.
This one works:
SELECT Table1.*
FROM Table1 Left Join Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*"
WHERE Table2.FieldMatch is Null
Click to expand...


This one does not work - the query never stops running
SELECT Table1.*
FROM Table1
WHERE Table1.FieldMatch In
(SELECT Table1.FieldMatch
FROM Table1 INNER JOIN Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch
Click to expand...
& "*")


This one does not work - it shows all rows from Table 1
SELECT Table1.*
FROM Table1, Table2
WHERE Table1.FieldMatch Not Like "*" & Table2.FieldMatch
Click to expand...
& "*"


Strangely enough, the one that works gives an error that
is can not represent the query in design view (A02), but
not matter.

Great help,
Thanks.
 
Back
Top