Why does this work? (Acc2K)

Topic

New Reply

I was trying to exclude several values from one field in a query Only one value is allowed in the field.

This didn’t work:
Not “value” Or Not ‘”Value” Or Not “Value” (it would still return everything).

Then I tried this, which works:
Not “value” And Not ‘”Value” And Not “Value”

I just wonder why the second one works. I thought the AND operator would only return values that were together in the same record.

Reply | Quote

Replies

Give the example you were trying to exclude the employees Billy, Brian, and Jeff. You say,
select * from MyTable where Name “Billy” or “Brian” or “Jeff”

When the query gets to the first record, it sees “Brian.” Brian does not = Jeff. Brian does not = Billy. So it fits. Because if you say OR, you are saying Anything besides Brian (which includes Billy or Jeff) or anything besides Billy (which includes Brian or Jeff), or anything besides Jeff (which includes Billy or Brian).

When you say AND, you are saying, it can’t be Brian, it can’t be Jeff, it can’t be Billy. Anything besides that is OK.

Hope that helps.

Reply | Quote

Thanks, I get it now.

Reply | Quote

Activate WHERE regarding the field of interest and enter as criteria:

NOT IN(“Val1″,”Val2”,…) where Val1, Val2,… are the values you want to exclude.

By the way, AND NOT “Val1” is equivalent to NOT IN(“Val1”).

Reply | Quote