SQL包含条件（如果不为null）

Question

在访问中，我一直试图在子窗体（检查清单）上显示的内容上设置用户过滤器。我尝试过研究的其他方法没有起作用，但是我成功使用了下面的代码，如果用户填写其他任何过滤器选项，它将进行过滤。我能弄清楚的是如何让它接受多个过滤器，除非我阐明了每种可能的组合框

因此，无论如何，这实际上是可能的，还是我需要看看其他选择？

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

实际上，如果表单过滤器不为空，有没有一种简单的方法可以包含条件

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

Answer 1

如果我理解正确，那么您将拥有一个包含几个未绑定控件的表单，用户将填写该表单。然后，您想要根据用户输入的内容来过滤子表单。在这种情况下，您尝试使用的方法将变得非常复杂且难以维护，更不用说处理大量数据的速度也很慢。最好的选择是在代码中构建过滤器。我通常要做的是将控件的名称与字段名称相同，然后将字段类型放入控件的tag属性中。然后遍历控件以构建过滤器。这是空运代码，但应该给您一个开始。
Dim strFilter as string Dim ctL as Access.control For Each ctL in Me.Controls If Not ISNull(ctL) And ctL.Tag <> "" Then strFilter = strFilter & " AND " & ctl.Name & " = " If ctL.Tag = "Text" Then strFilter = strFilter & "'" & Replace(ctl, "'","''") & "'" ElseIf ctL.Tag = "Date" Then strFilter = strFilter & "#" & ctl & "#" Else strFilter = strFilter & ctlEnd If Next If strFilter <> "" Then strFilter = mid(strFilter, 6) strFilter = " Where " & strFilter End If Me.yourSubForm.Form.Recordsource = "Select * From <yourQuery> " & strfilter