MS Access Query to determine if 1+ fields of a subset of fields is not null
I recently encountered an issue where I tried to determine which records for any of 7 out of 12 Forest Service National Forests contained data for a query. Region 4 of the USFS has 12 NFs, of which 7 have boundaries within Idaho. For a list of sensitive species occurring in the region, I wanted to determine which species occurred in 1 or more of the forests that occur in Idaho. Currently, R4 indicates 231 sensitive species, but only 56 occur in forests within Idaho.
With my original query, I simply put `Is Not Null' in the `or:' row of the query design view, but this resulted in no records being returned. It appeared that something needed to be in the `Criteria:' row of query design, but when I did this, then put `Is Not Null' in the `or' row going straight across for each field desired, only records meeting the `Criteria:' rule were returned. Example of failed query shown below:
It ended up that the query rules needed to be "tiered" or staggered in the query design view, with each OR statement completely encapsulated with parenthesis, resulting in the following GOOD SQL query:
What is this?
We use the IFWIS Blog to keep our staff, cooperators and the public updated on what we are working on, what's new and share tips about using our software and website.
The content of the IFWIS Blog is often highly technical, but we try our best to keep our posts accessible and jargon-free. When this is not possible, we'll link to other resources online to provide background. Thanks for reading and please follow our blog for updates.
- IFWIS RSS Feed
- Daily Email Digest (All Public Content)
- Or follow just one of the many topic-specific tag feeds or pages below.
- 1 of 6