CheckBox to drive subform query

[djkay2637]

Good afternoon all.

I am seeking assistance with a more complex query that maybe needs nested iff statements. I will try and explain the scenario.

This all works / background
My main form contains simple data about a customer. When the user clicks on the communications tab there is a sub form that shows only the communications for that customer. This all works fine. My original issue is that some customers have may communication notes and it can get a bit troublesome scrolling through so I decided to implement a filter. At the moment the sub form is driven by a query that now contains a "reason code" with combo box options for each record / communication log. My parent form contains a combo box with the same options and on the 'after update" function I am running the

Me.ComNotes.Requery

function to get the subform to refresh. My query criteria for the 'reason code' field is

[forms]![Frm]![SearchComsFilter]

This all works fine in limiting the subform results based on the reasoncode.

Here is the problem

After using this new feature I realise that the end user wants to actually eliminate reasons not applicable to them not just look for see one reason at a time. This means I need to rethink the filter.

What I want

I want to get rid of the filter combo box on the parent form and create checkboxes for the available options. All the checkboxes by default will be true / -1 (or what ever the value is). I then want the query to display all results in the sub form if all checkboxes are true. Upon each checkbox's 'after update' I want the form to requery to update the filter. My issue is I don't know how to built the query criteria for the 'reasonCode' field.
If I use

 iff([Forms]![Frm]![PreSalesCheckbox]=-1,"presales",Null)

It may now allow me to just display the 'Presales' reason code. But what about all the others? Will I need to use the '&' at the end of the first iff and have an iff for every option? What if the query criteria contains a null as a consequence of the previous iff? Do I need to use the 'OR' options when building the query criteria? This is my dilemma. I just cant get it to work. :banghead:

Screenshots attached to show my new UI

Many thanks for reading this and thank you in advance if you have any suggestions.

Regards
Dan

 

[Ranman256]

If you need news tested IIF statements, instead,
Use a conversion table to convert 1 value to another.
Or
Create a Function , the would accept a field, then apply all riles needed ,the return the value you need.

 

[djkay2637]

I don't quite understand. Essentially if i wanted all results to show the criteria would enter manually would be

"Presales" OR "Finance" OR .....

going through all my options. This would ensure each item displayed. I want the tickbox / IIF statements to generate that string within the criteria.