Query Filter using Not In

[DEB]

I want to filter a query to not show a list of values. I know to use Not
Iin('something', 'something'), but what if I want to use a wildcard? How do
I do a not in query using a wildcard? Thanks!

 

[Michel Walsh]

Use a temp table, one field, CompareTo, to hold all the values, in
different rows, one value you want to compare to, per row. Without wild
card, use an equality:

SELECT whatever
FROM table1 LEFT JOIN tempTable
ON table1.fieldName = tempTable.CompareTo
WHERE tempTable.CompareTo IS NULL


that is, indeed, something similar that the query wizard about finding
unmatched record would produce.


With wildcard, have the values holding the proper wildcard, and use like:

SELECT whatever
FROM table1 LEFT JOIN tempTable
ON table1.fieldName LIKE tempTable.CompareTo
WHERE tempTable.CompareTo IS NULL





Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

You can't use a wild card with the IN operator. You would have to build the
query using mulitple AND statements

WHERE FieldA Not Like "xx*" AND FieldA Not Like "rpxt*" AND FieldA Not Like
"*C*"

Or you could state that as
WHERE Not(FieldA Like "xx*" Or FieldA Like "rpxt*" OR FieldA Like "*C*")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..