Exclude query data

[Guest]

I need a method of excluding data from a field in a query. One of the
columns lists several different item codes and I want to exclude 3 of the
codes. How can I perform a 'not equal to' for more than one code?

 

[Jeff Boyce]

First off, storing more than "one fact per field" is a recipe for ... (oh
yes, you are finding that out!). If you have a one-to-many relationship,
instead of stuffing many facts into a single field, create a new (related)
table and use one fact per field.

That said, even though it is not a good idea to store data that way, you can
use wildcards and the "Like" command to do what you are asking. In the
Selection Criterion "cell" under the field in your query (design view), put
something like:

Not Like *abc* And Not Like *xyz* And Not Like *999*

Naturally, you'll put the codes that you are trying to exclude between the
*'s.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I need a method of excluding data from a field in a query. One of the
columns lists several different item codes and I want to exclude 3 of the
codes. How can I perform a 'not equal to' for more than one code?

Use a criteria like:

<> c1 And <> c2 And <> c3 And . . .
or
Not IN(c1, c2, c3, ... )

If you code field is a Text field then enclose each code in
quotes.