Query Criteria not filtering out correctly

[Dan]

I am creating a query off of a single table. One column/field in the database
contains a vendor code that consists of a five-character name. I want the
query to bring back all records for all vendors, except for 6 distinct
vendors.

Let's assume these vendors are "ABCDE", "ZYXWV" and so forth.

When setting up my query, I am typing the following into the criteria boxes:

Not "ABCDE"
Not "ZYXWV"

and so forth.

The query that returns includes records with the vendor codes I have tried
to omit.

This also occurs when I use the following in a single criteria box:
<>"ABCDE" Or <>"ZYXWV" and so forth.

Any ideas what might be going wrong here? Many thanks in advance.

 

[fredg]

I am creating a query off of a single table. One column/field in the database
contains a vendor code that consists of a five-character name. I want the
query to bring back all records for all vendors, except for 6 distinct
vendors.

Let's assume these vendors are "ABCDE", "ZYXWV" and so forth.

When setting up my query, I am typing the following into the criteria boxes:

Not "ABCDE"
Not "ZYXWV"

and so forth.

The query that returns includes records with the vendor codes I have tried
to omit.

This also occurs when I use the following in a single criteria box:
<>"ABCDE" Or <>"ZYXWV" and so forth.

Any ideas what might be going wrong here? Many thanks in advance.

Did you try
Where YourTable.FieldName Not In("ABCDE","ZYXWZ","etc")

It's always best to post the full query SQL... at least the full Where
clause.

 

[Dan]

Thanks for the response -- I am not fluent in SQL, and I am using the Query
Design function.

It appears as if the relevant SQL view of the query is:

HAVING ((Not(Report.Vendor)="ABCDE")) OR ((Not(Report.Vendor)="ZYXWV"))

Where Report is the name of the table, and vendor is the field name of the
vendor code data in the table.

 

[KARL DEWEY]

Use this ---
<>"ABCDE" AND <>"ZYXWV" AND ....

 

[John W. Vinson]

Thanks for the response -- I am not fluent in SQL, and I am using the Query
Design function.

It appears as if the relevant SQL view of the query is:

HAVING ((Not(Report.Vendor)="ABCDE")) OR ((Not(Report.Vendor)="ZYXWV"))

Where Report is the name of the table, and vendor is the field name of the
vendor code data in the table.

Just as a (possibly more compact) alternative to Karl's and Fred's correct
advice, I'd suggest two things:

1. Change the Totals line under the Vendor field to "Where" (so it will use a
WHERE clause applied before the sums and totals operations are performed,
making it more efficient).

2. Use a criterion of

NOT IN ("ABCDE", "ZYXWV", "PDQXY")


Do note that the query grid is *not* the query. It's just a tool to build SQL;
for simple to moderate queries it's quite a good tool - but you can go much
further by using the actual language of queries, that is, SQL. There are
queries that can ONLY be created in SQL view, and there are others - multiple
levels of AND and OR logic - which are much clearer in SQL view.

 

[k2harrell]

Even is grid view of the query, you can enter the following on the criteria
line of your vendor column: Not In("ven1","ven2","ven3", etc.) as other
answer indicated. Just break up the list of vendor names with a comma between
them and be sure to put the literal names to be excluded in quotes.