Criteria in query <> isn't working with multiple codes

T

TJAC

I have a query and in one field I am looking for all of the codes, except 6.
There are too many codes to say I only want to see these...In the criteria I
have entered it as <>AB, or <>CD, etc. I have also tried going down the rows
for "or" but none of this has worked. It will only work with one code.

Does anyone have any suggestions or can you tell me why this isn't working?

Thanks!
 
K

Kerry

Hi

It is working, it just isn't doing what you expected. If a code is AB
it is true that it is <>AB or <>CD, because it is not CD. I think you
are looking for <>AB AND <>CD, etc. You could also use in, for
example not in ("AB","CD","ETC")
 
K

Ken Sheridan

You need to repeat the column name for each Boolean AND operation. In SQL it
would be:

WHERE YourColumn <> "AB"
AND YourColumn <> "CD"
AND YourColumn <> "DS"

In query design view you'd add the column to the grid once for each value
you need to exclude, uncheck the 'show' check box for all but the first and
in the criteria row of the first put the <> "AB", in that of the second <>
"CD" and so on.

Using a NOT IN operation with a value list as Kerry suggests is simpler,
however.

Ken Sheridan
Stafford, England
 
Top