NOT operator

[LSSR]

I have a text field in Access 2003 with a couple of hundred different values.
I am trying to create a query to exclude some 20 values. I have used NOT
LIKE “value 1†or “value 2†or “value 3†etc. Value 1 is excluded but the
others following are not. I have also tried NOT by itself. I have
entered this as one line in the criteria space, as well as individual line
for each value.. At times Access has put each value in its own line. Any
suggestions? Thank you.

 

[Allen Browne]

You need to bracket the value so the NOT applies to all of them, i.e.:
NOT ("value1" or "value2" or ...)

It might be more efficient to use IN, e.g.:
NOT IN ("a", "b", "c")

BTW, if this is applied against a field of type Number, omit the quotes,
e.g.:
NOT IN (1,2,3)

 

[LSSR]

Allen- It worked perfectly. Thanks for helping me (and others that ask
questions)

 

[Marshall Barton]

I have a text field in Access 2003 with a couple of hundred different values.
I am trying to create a query to exclude some 20 values. I have used NOT
LIKE “value 1” or “value 2” or “value 3” etc. Value 1 is excluded but the
others following are not. I have also tried NOT by itself. I have
entered this as one line in the criteria space, as well as individual line
for each value.

A comletely different approach that is more general as well
as more efficient is to create a table with one record for
each value you want to exclude. Your query would then look
like something the unmatched query wizard would generate.

SELECT T.*
FROM yourtable As T LEFT JOIN tblExcludeList As X
ON T.thefield = X.Exclude
WHERE T.thefield Is Null

This make it very easy to manage a changing list of things
to exclude.