Not Like

[Stefan]

I have a table with a large list of codes.

I need to create a query which will filter out a lot of different codes too
many to really list.

I am thinking I would do multiple criteria Not Like "L*" and Not like "S9*"
etc...

I probably need up to about ten of these filters

What is the best way to code this in a query without causing it to bog down
too much.

Thanks in advance

 

[Allen Browne]

10 filters will be okay. Perhaps:
Not (Like "L*" OR Like "S9*" OR ...)

If there are only 10 discrete values (i.e. you don't really need the
wildcards), this might be more efficient:
NOT IN ("L", "S9", ...)

Alternatively, it might be worth creating a little lookup table that
categorizes your record, e.g.:
L 1
S9 1
ZZ 2
You could then include this table in your query, and exclude the type 1
records.

 

[John W. Vinson]

I have a table with a large list of codes.

I need to create a query which will filter out a lot of different codes too
many to really list.

I am thinking I would do multiple criteria Not Like "L*" and Not like "S9*"
etc...

I probably need up to about ten of these filters

What is the best way to code this in a query without causing it to bog down
too much.

Thanks in advance

One thing to try - and I'm not at all sure it will work efficiently! - is a
non-equi join to an Exclusions table. Create a table Excludes with one text
field ToExclude, with each row containing a code (prefix) to be excluded:

L
S9
LEAVEMEOUT

and so on.

Then use a query

SELECT yourtable.*
FROM yourtable
RIGHT JOIN Excludes
ON yourtable.Codefield LIKE Excludes.ToExclude & "*"
WHERE ToExclude IS NULL;