using the Or criteria

  • Thread starter Thread starter Barbara
  • Start date Start date
B

Barbara

I have a query that in addition to the name and address fields has 4 fields
named using iif statements. I need criteria to select names based on a "Yes"
entry in any of these 4 fields.

I have tried putting <>" " in the criteria field in Field 1 and the same in
the "or" field of Fields 2-4 and the result is names that are "yes" in Field
1 and some variety of 2-4.
 
It is probably easier to edit the criteria in the SQL view. Spot the WHERE
clause and instead of:

WHERE field1 <> 0 AND field2 <> 0

try


WHERE field1 <> 0 OR ( field2 <> 0 AND field3 <> 0)


note that you can use parenthesis to specify the order of evaluation.

Also, it is preferable to use "<> 0" and "= 0" to say "is true" and
"is false", since 0 is false, and anything else, not null, is true.


Vanderghast, Access MVP
 
SELECT SomeField FROM TableName WHERE [Field1] + [Field2] + [Field3] +
[Field4] <> 0

This assumes the Field1 - Field4 are Yes/No (Boolean) data types. No
(False) returns 0 and Yes(True) returns -1. If any one of the fields is
True, then a non zero value will be returned.
 
Back
Top