AND criteria not working (1 Viewer)

chuckcoleman · Mar 4, 2019

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 · Mar 4, 2019

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 · Mar 4, 2019

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 · Mar 4, 2019

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 · Mar 4, 2019

The_Doc_Man said:
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 · Mar 4, 2019

plog said:
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 · Mar 4, 2019

Try

Code:

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

The_Doc_Man · Mar 4, 2019

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.

AND criteria not working (1 Viewer)

chuckcoleman

Registered User.

Minty

AWF VIP

plog

Banishment Pending

The_Doc_Man

Immoderate Moderator

chuckcoleman

Registered User.

chuckcoleman

Registered User.

bdra2778

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread