Access form checkbox AND OR statements

Question

Recently I started creating a form to filter the database by using checkboxes.

I have the following data:

Category  Type   Country  
Fruit     Apple  NL   
Fruit     Apple  SU   
Fruit     Banana NL   
Fruit     Banana DE   
Nuts      Cashew NL   
Nuts      Cashew US   
Nuts      Almond UK   
Nuts      Almond GR   

Now I build in my form the checkboxes for Fruit & Nuts, for Apple, Banana, Cashew & Almond and for NL, SU, DE, US, UK & GR.

At this moment I use only the OR statement in my SQL script, which works well. But if the user would like to know for example which fruit comes from NL and it marks both Fruit and NL then my output will be everything from Fruit and everything from NL (because of the OR statement).

How should I change my script that the filter is used based on the checkboxes that are checked? In my example: if the user checks both Fruit & NL then it will give the output --> Fruit Apple NL & Fruit Banana NL

Or another example: He wants Banana & Cashew, and gets as output --> Fruit Banana NL & Fruit Banana DE & Nuts Cashew NL & Nuts Cashew US

I hope my problem is clear; if not, do not hesitate to ask for clarification!

Answer 1

From what I understand you have a form with 2 Category check boxes, 4 Type check boxes and 6 Country check boxes.

It doesn't sound like any of these filter themselves, so it's possible try and return Nuts, Apple and UK which would return an empty table.

I think you'll have to make extensive use of the LIKE command so any NULL/FALSE values can be converted to *.

As a checkbox returns TRUE/FALSE rather than Apple/False an IIF statement can be used to change TRUE to Apple (you could probably use CHOOSE or SWITCH instead).

So the WHERE clause here says chkFruit is either Fruit or anything.

SELECT  Category, Type, Country
FROM    Table2
WHERE   Category LIKE (IIF([Forms]![Form1]![chkFruit],'Fruit','*')) AND
        Category LIKE (IIF([Forms]![Form1]![chkNuts],'Nuts','*')) AND
        Type LIKE (IIF([Forms]![Form1]![chkApple],'Apple','*')) AND
        Type LIKE (IIF([Forms]![Form1]![chkBanana],'Banana','*')) AND
        Type LIKE (IIF([Forms]![Form1]![chkCashew],'Cashew','*')) AND
        Country LIKE (IIF([Forms]![Form1]![chkNL],'NL','*')) AND
        Country LIKE (IIF([Forms]![Form1]![chkSU],'SU','*')) AND
        Country LIKE (IIF([Forms]![Form1]![chkDE],'DE','*'))  

Edit:
Reading your comment below it sounds like you want it to filter by the ticked boxes, or if no boxes are ticked then show all of them (as if all boxes in the Category/Type or Country were ticked).
That could get quite complicated if you have lots of tick boxes.

So each section of check boxes needs breaking down to something similar to below.

WHERE   ((
        Category = IIF([Forms]![Form1]![chkFruit],'Fruit',NULL) OR
        Category = IIF([Forms]![Form1]![chkNuts],'Nuts',NULL)
        ) OR IIF(NOT [Forms]![Form1]![chkFruit] AND 
                 NOT [Forms]![Form1]![chkNuts],Category LIKE '*',NULL))  

The full SQL would be:

SELECT  Category, Type, Country
FROM    Table2
WHERE   ((
        Category = IIF([Forms]![Form1]![chkFruit],'Fruit',NULL) OR
        Category = IIF([Forms]![Form1]![chkNuts],'Nuts',NULL)
        ) OR IIF(NOT [Forms]![Form1]![chkFruit] AND 
                 NOT [Forms]![Form1]![chkNuts],Category LIKE '*',NULL))
        AND
        ((
        Type = IIF([Forms]![Form1]![chkApple],'Apple',NULL) OR
        Type = IIF([Forms]![Form1]![chkBanana],'Banana',NULL) OR
        Type = IIF([Forms]![Form1]![chkCashew],'Cashew',NULL) OR
        Type = IIF([Forms]![Form1]![chkAlmond],'Almond',NULL)
        ) OR IIF(NOT [Forms]![Form1]![chkApple] AND 
                 NOT [Forms]![Form1]![chkBanana] AND
                 NOT [Forms]![Form1]![chkCashew] AND
                 NOT [Forms]![Form1]![chkAlmond],Type LIKE '*',NULL))
        AND
        ((
        Country = IIF([Forms]![Form1]![chkNL],'NL',NULL) OR
        Country = IIF([Forms]![Form1]![chkSU],'SU',NULL) OR
        Country = IIF([Forms]![Form1]![chkDE],'DE',NULL) OR
        Country = IIF([Forms]![Form1]![chkUS],'US',NULL) OR
        Country = IIF([Forms]![Form1]![chkUK],'UK',NULL) OR
        Country = IIF([Forms]![Form1]![chkGR],'GR',NULL)
        ) OR IIF(NOT [Forms]![Form1]![chkNL] AND
                 NOT [Forms]![Form1]![chkSU] AND
                 NOT [Forms]![Form1]![chkDE] AND
                 NOT [Forms]![Form1]![chkUS] AND
                 NOT [Forms]![Form1]![chkUK] AND
                 NOT [Forms]![Form1]![chkGR], Country LIKE '*',NULL))  

I've got a feeling this could be shortened quite a bit but it's starting to hurt my head now.