AND on 2 fields along with other criteria in query

[William R. Hess]

Hello
New to Access and trying to figure out a problem with an AND test

I have a database with 4 fields called A B C D

Field A Must =1 (no problem with this)

Field D Must > 1 (Again no problem)

Fields B & C can be any number including zero, as long as BOTH fields are
not zero. If I search using the criteria for fields A & D only, I find 390
records. If I search with criteria that says show me if both are zero,
(B=0, C=0 on the criteria line, along with the other criteria) it shows me
the 4 or so suspect records that have a zero in BOTH fields. If I use the
opposite, and use B<> 0, C<> 0, A =1 & D>1 on the criteria line, it is
returning 241 records. It appears to be omitting the records if EITHER of
the fields are zero

Is there a way where I'm supposed to 'enclose' the criteria for B & C in
parenthesis ala Excel so they are evaluated together?

Suggestions or ideas appreciated!

-Bill

 

[Allen Browne]

Underneath the Critiera row in query design are several rows with "Or" at
the left.

Under B, enter this on the Criteria line:
<> 0

Under C, enter this on the first Or line below:
<> 0

If you have any other criteria, repeat them on the first Or line as well.

Alternatively if you prefer to work in SQL View, just bracket the OR
conditions, e.g.:
WHERE (A = 1) AND ((B <> 0) OR (C <> 0)) AND (D > 1)

 

[William R. Hess]

Thank you Allen!

I typed in your SQL statement and it worked great. In the Design view, it
shows the criteria for A & B on the criteria line, with the criteria for C &
D showing up on the OR line. I figured out (by your example) that by using
AND I was eliminating any and all zeros in the fields which is why the
results returned were so low.

Thank you again for your help!

-Bill