AND criteria not working

[chuckcoleman]

OK, I'm feeling kind of dumb this morning. I have a test table with two fields, Field1 and Field2. The data in the table is:

Field1 Field2
a a
a b
b a
b b
c d

If I run a query where Field1 = "b" and Field2 = "b", no problem. I get one row where Field1 = "b" and Field2 = "b". The criteria is on the same row in the Query grid.

If I run a query where Field1 <> "b" AND Field2 <>"b", (on the same "And" row), I get on row 1 "a" and "a" and on row 2 I get "c" and "d". I don't know why I don't ALSO get on row three "a" and "b" and on row four "b" and "a". I THINK my criteria is saying, "Give me all records where Field1 is not equal to "b" AND Field2 is not equal to "b". Both criteria has to be met.

What am I missing? Not enough coffee?

 

[Minty]

Yup - more coffee.

The criteria are indeed AND when on the same line in the QBE editor.

If you don't want either to be "B" then simply move one criteria down aline to make it an OR

 

[plog]

Step through the logic. Here's the results of a query that does each test to see if a value is Not B:


F1, F2, F1 Not B?, F2 Not B?
a, a, True, True
a, b, True, False
b, a, False, True
b, b, False, False
c, d, True, True


You have asked your query to return the records where [F1 Not B?] and [F2 Not B?] are both true.

a, b passes one test([F1 Not B?]) but fails the other ([F2 Not B?]). To pass your criteria only True/True records get through.

 

[The_Doc_Man]

Let's look at the logic behind what you are doing.

You start with looking for ("Field1 = b" AND "Field2 = b"). You get the expected result.

If you are now trying to find the complementary set, you need to apply DeMorgan's Theorem. Thus, the complementary criteria would be ("Field1 <> b") OR ( "Field2 <> b" ). Given your input set, you would get four rows: <a,a>, <a,b>, <b,a>, and <c,d>.

Is that what you wanted to get when you reversed the "=" to "<>" ?

 

[chuckcoleman]

Let's look at the logic behind what you are doing.

You start with looking for ("Field1 = b" AND "Field2 = b"). You get the expected result.

If you are now trying to find the complementary set, you need to apply DeMorgan's Theorem. Thus, the complementary criteria would be ("Field1 <> b") OR ( "Field2 <> b" ). Given your input set, you would get four rows: <a,a>, <a,b>, <b,a>, and <c,d>.

Is that what you wanted to get when you reversed the "=" to "<>" ?

DOC Man, yes, I was expecting to get four rows, <a,a>, <a,b>, <b,a>, and <c,d>, when on the QBE grid, on one row I had Field1 <>"b" and Field2 <>"b"

 

[chuckcoleman]

Step through the logic. Here's the results of a query that does each test to see if a value is Not B:


F1, F2, F1 Not B?, F2 Not B?
a, a, True, True
a, b, True, False
b, a, False, True
b, b, False, False
c, d, True, True


You have asked your query to return the records where [F1 Not B?] and [F2 Not B?] are both true.

a, b passes one test([F1 Not B?]) but fails the other ([F2 Not B?]). To pass your criteria only True/True records get through.

Thanks. I do understand your logic and it makes sense. It's just kind of weird that if the criteria is ="b" the AND criteria works but when it's <> "b" it can't be on the same row. In one sense it's like a double-negative. Maybe that's why I'm not thinking about it the right way.

 

[bdra2778]

Try

Not (field1="b" AND field2="b")

 

[The_Doc_Man]

BDRA's suggestion is the other way to invert the expression.

De Morgan's Theorem (that I referenced) says:

NOT ( A AND B ) = ( NOT A ) OR ( NOT B )

NOT ( A OR B ) = ( NOT A ) AND ( NOT B )

BDRA took the negation of the expression as a whole. I used De Morgan's Theorem to negate the expression piecemeal.