访问表单复选框AND OR语句 [英] Access form checkbox AND OR statements

问题描述

最近，我开始使用复选框创建表单以过滤数据库.

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

我有以下数据:

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   

现在，我在表单中建立了Fruit&苹果，香蕉，腰果和坚果的果仁.杏仁以及NL，SU，DE，美国，英国& 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.

此刻，我在SQL脚本中仅使用OR语句，效果很好.但是，如果用户想知道例如哪个水果来自NL，并且同时标记了Fruit和NL，那么我的输出将是来自Fruit的所有内容以及来自NL的所有内容(由于OR语句).

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).

如何根据选中的复选框更改使用过滤器的脚本?在我的示例中:如果用户同时选中了Fruit& NL然后它将给出输出-> Fruit Apple NL&水果香蕉NL

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

或另一个例子:他想要香蕉&腰果，并作为输出-> Fruit Banana NL&水果香蕉DE&坚果腰果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!

推荐答案

据我了解，您有一个带有2个 Category 复选框，4个 Type 复选框和6 Country (国家/地区)复选框.

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

听起来不像这些过滤器本身，因此可以尝试返回 Nuts ， Apple 和 UK 一个空桌子.

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.

我认为您必须广泛使用LIKE命令，以便任何NULL/FALSE值都可以转换为*.

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

当复选框返回TRUE/FALSE而不是Apple/False时，可以使用IIF语句将TRUE更改为Apple(您可以使用CHOOSE或SWITCH代替).

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).

所以这里的WHERE子句说chkFruit是水果或任何东西.

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','*'))  

修改:
在下面阅读您的注释，听起来像是希望它被选中的框过滤，或者如果没有选中任何框，则将其全部显示(就像选中了类别/类型"或国家/地区"中的所有框一样).
如果您有很多复选框，那可能会变得非常复杂.

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))  

完整的SQL将是:

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.

这篇关于访问表单复选框AND OR语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！