SQL包含条件在Where if not not null

在访问中,我一直在尝试在子表单中显示的内容上设置用户过滤器,子表单是检查列表.我尝试过的其他方法没有工作,但我已经成功使用下面的代码,如果用户填写任何其他过滤器选项,它将过滤.我能弄清楚的是如何让它接受多个过滤器,除非我拼写出每个可能的盒子组合

那么无论如何这实际上是可能的,还是我需要查看其他选项？

WHERE [STATUS] = "OPEN" 
AND 
(ANY FORM FILTERS is not Null [Filter by all those that are not null to its matching column])

如果表单过滤器不为null,有效的方法是包含条件

SELECT Inspections.INS_ID,Inspections.Category,Inspections.Assigned_Officer,Inspections.Raised_For,Inspections.Account,Inspections.Number,Inspections.Street,Inspections.Area,Inspections.Postcode,Inspections.Date_Raised,Inspections.Reason,Inspections.INS_Comments
FROM Inspections
WHERE (
        ((Inspections.STATUS) = "Open")
        AND (([Forms]![Manage_Open]![Filter_ID]) IS NULL)
        AND (([Forms]![Manage_Open]![Filter_account]) IS NULL)
        AND (([Forms]![Manage_Open]![Filter_officer]) IS NULL)
        AND (([Forms]![Manage_Open]![Filter_Number]) IS NULL)
        AND (([Forms]![Manage_Open]![Filter_Postcode]) IS NULL)
        AND (([Forms]![Manage_Open]![Filter_Category]) IS NULL)
        AND (
            (([Forms]![Manage_Open]![Filter_From]) IS NULL)
            AND (([Forms]![Manage_Open]![Filter_To]) IS NULL)
            )
        )
    OR (
        ([Forms]![Manage_Open]![Filter_ID]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_ID]) = [Inspections].[INS_ID]
        )
    OR (
        ([Forms]![Manage_Open]![Filter_account]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_account]) = [Inspections].[Account]
        )
    OR (
        ([Forms]![Manage_Open]![Filter_officer]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_officer]) = [Inspections].[Assigned_Officer]
        )
    OR (
        ([Forms]![Manage_Open]![Filter_Number]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_Number]) = [Inspections].[Number]
        )
    OR (
        ([Forms]![Manage_Open]![Filter_Postcode]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_Postcode]) = [Inspections].[Postcode]
        )
    OR (
        ([Forms]![Manage_Open]![Filter_Category]) IS NOT NULL
        AND ([Forms]![Manage_Open]![Filter_Category]) = [Inspections].[Category]
        )
    OR (
        (
            (([Forms]![Manage_Open]![Filter_From]) IS NOT NULL)
            AND (([Forms]![Manage_Open]![Filter_To]) IS NOT NULL)
            )
        AND ([Inspections].[Raised_For]) BETWEEN (
                    ([Forms]![Manage_Open]![Filter_From])
                    AND ([Forms]![Manage_Open]![Filter_to])
                    ) )
        );

解决方法

如果我理解正确,您有一个表单,其中包含用户将填写的多个未绑定控件.您可以根据用户输入的内容过滤子表单.
在这种情况下,您尝试使用的方法将变得非常复杂且难以维护,更不用说大量数据的速度慢.
最好的办法是在代码中构建过滤器.
我经常做的是将控件命名为与字段名称相同,并将字段类型放在控件的标记属性中.然后遍历控件以构建过滤器.这是航空代码,但应该给你一个开始.

将strFilter调暗为字符串Dt ctL as Access.control对于Me.Controls中的每个ctL如果不是ISNull(ctL)和ctL.Tag<> ““ 然后strFilter = strFilter& “和”& ctl.Name& “=”如果ctL.Tag =“Text”那么strFilter = strFilter& “’”&替换(ctl,“’”,“””)& “’”ElseIf ctL.Tag =“Date”然后strFilter = strFilter& “#”& ctl& “#”其他strFilter = strFilter& ctlEnd如果下一个如果strFilter<> ““ 然后strFilter = mid(strFilter,6)strFilter =“Where”& strFilter的万一Me.yourSubForm.Form.Recordsource =“选择*来自< yourQuery>”& strFilter的

SQL包含条件在Where if not not null

解决方法

猜你在找的MsSQL相关文章