Query is making a nuts

[Craig]

Hi

Using Access 2007

I have a table and two of the fields (Status and OrigStatus) have, among
other possible entries, the words "Member," "Customer" or "Request."

I am trying to create a query that only displays records which do NOT have
"Member," "Customer" or "Request" in either field.

But it won't work!

I have tried putting each word in its own Criteria column in each field
using syntax such as:

<>"Member"

with no success.

I have also tried

<>"Member" OR "Customer" OR "Request"

with no success.

What am I doing wrong? How should I do this?

Thanks!

 

[Craig]

Making ME nuts; not "a nuts"

To clairfy:

When I say "with no success," I mean records with Member, Customer or
Request in either field are still appearing.

Thanks,

 

[Haggis]

in SQL

(Status<>["Member","Customer","Request"]) or
(OrigStatus<>["Member","Customer","Request"])

hth

 

[John Spencer]

Use

Not in ("Member","Customer","Request")

as the criteria for both fields.

The criteria should be on the same line so that the criteria is ANDed
together not Or'd.

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

 

[Craig]

Hi

When I use

Not in ("Member","Customer","Request")

as the criteria for both fields in one line, then no records display at all
even though many of them do not contain the aforementioned words.

Using

Not in ("Member","Customer","Request")

in separate criteria lines doesn't work either as some records that
shouldn't still leak through.

As I said, this is making me nuts!

 

[Rob Parker]

Hi Craig,

Using a NOT criteria will also eliminate null records from the dataset. So,
if your table has an entry in one field which doesn't match, and a null in
the other, it will also be excluded. To cover this possibility, enter the
following in a single criteria field for each field:

Not in ("Member","Customer","Request") OR Is Null

HTH,

Rob

 

[Craig]

It did help!

That's the solution. Funny things is, I was just consider the "Null factor"
so to speak -- good timing with your post!

Thanks,

Craig