Query records by "no" criteria (1 Viewer)

[bpc1974]

I have many records in a maintenance database with "yes/no" datatypes. I created one query with a date parameter and now want to report records where any one field has a "no." How can I do this? Can I run the date parameter query within this other query and then set the criteria for each field to "no", or must I take a different approach? Any help appreciated.

 

[EndersG]

Sure you can. Create a query that contains only the fields with Y/N datatypes. Include the date parameter field in this query. Create a user-defined field (AtLeastOneNo) that calculates the product of all the Y/N fields. For example, "AtLeastOneNo: [YNField1]*[YNField2]*[YNField3]*[YNFieldN]"

The logic behind this is that a No returns a 0 value whereas a Yes returns a non-zero value. The product of any record with at least one No will return a zero whereas a record without a No will return a non-zero value. Under the Criteria field, type in "0" so that it will only pull in records where AtLeastOneNo is 0, or there is a record with a No value and you can use the date parameter to select within a specified date range.

HTH.