idfg-badge

Idaho Fish and Game

MS Access Query to determine if 1+ fields of a subset of fields is not null

idfg-jstrickland

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:

 

SELECT [Tbl_USFS-R4--July_2011].ID, [Tbl_USFS-R4--July_2011].Type, [Tbl_USFS-R4--July_2011].Species, [Tbl_USFS-R4--July_2011].Common, [Tbl_USFS-R4--July_2011].Boise_NF, [Tbl_USFS-R4--July_2011].Caribou_NF, [Tbl_USFS-R4--July_2011].Challis_NF, [Tbl_USFS-R4--July_2011].Payette_NF, [Tbl_USFS-R4--July_2011].Salmon_NF, [Tbl_USFS-R4--July_2011].Sawtooth_NF, [Tbl_USFS-R4--July_2011].Targhee_NF
 
FROM [Tbl_USFS-R4--July_2011]
 
WHERE ((([Tbl_USFS-R4--July_2011].Boise_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Caribou_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Challis_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Payette_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Salmon_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Sawtooth_NF) Is Not Null)) OR ((([Tbl_USFS-R4--July_2011].Targhee_NF) Is Not Null));
 
Tiered Query to determine if any of a subset