Using MS Acess 2016 to remove specifc fields of a record while still displaying the entire dataset within a query

Question

I am looking to query a dataset of mine in MS Access 2016 where only specific strings are included within the query's output (please take a look at the included image). I am looking to include all fields with strings that contain the word "rat", "mouse", "stoat", and "possum" from the "Pest_com" columns while all other fields within those columns are excluded.

Normally I would have used a left join from the parent table with subsequent operators, but I am looking to keep all records of the table. Setting up a query in this manner seems to filter out records that do not include the strings I am looking for. Instead, I want to keep all records within the query and just remove the fields that do not contain my desired info.

Thanks in advance for your help!

enter image description here

Answer 1

What I think you want is to show the value in the field if the value matches your test list. In this case I would use the IIF function. The First parameter is a logical test in which you need to determine if there is a match. If it is, display the value otherwise, display a blank.

In the QBE you could replace Pest_Com1 with the below where the use of tbl is the name of the table.

Pest_COM1: IIF (tbl.Pest_COM1 LIKE "*rat*" OR tbl.Pest_COM1 LIKE "*mouse*" OR tbl.Pest_COM1 LIKE "*stoat*" OR tbl.Pest_COM1 LIKE "*possum*",tbl.Pest_COM1, "")

You can just repeat this for the additional columns. For something like this I often paste it into notepad and do a replace on the field name then copy back to the query rather than manually editing each case.

If you literally only want to include the relevant fields, you will have to construct the SQL string in vba code. You will have to determine if there are values in each field to determine whether to include. You can use a similar IIF to above for this but replace the true part (second parameter) with 1 and false part (third parameter) with 0. Then sum. If the field has a value > 0, you want to include the field. That is just the query portion.