SQL包含条件(如果不为null) [英] SQL to include condition in Where if not null

问题描述

在访问中，我一直在尝试针对子窗体(检查清单)中显示的内容设置用户过滤器.我尝试研究的其他方法没有奏效，但是我成功使用了以下代码，如果用户填写其他任何过滤器选项，它将进行过滤.我能弄清楚的是如何让它接受多个过滤器，除非我说明了所有可能的组合框

In access I have been trying to set up user filters on what is displayed in a subform which is a list of inspection. Other methods Ive tried looking into have not been working but I have success with the below code, It will filter if a user fills any other the filter options. What I can figure out is how to have it accept multiple filters unless I spelt out every possible combination of boxes

那么无论如何，这实际上是可能的，还是我需要看看其他选择?

So is there anyway this is actually possible or do I need to look at other options?

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

如果表单过滤器不为空，有效地有什么简单的方法可以包含条件

effectively is there any easy way to include a condition if a form filter is not 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])
                    ) )
        );

推荐答案

如果我正确理解，您将拥有一个表单，其中包含几个用户将要填写的未绑定控件.您想要根据用户的内容过滤子表单已经进入. 在这种情况下，您尝试使用的方法将变得非常复杂且难以维护，更不用说处理大量数据的速度很慢. 最好的选择是在代码中构建过滤器. 我通常要做的是将控件的名称与字段名称相同，然后将字段类型放入控件的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

If I understand correctly, you have a form with several unbound controls that the user will fill in. You, then want to filter your subform according to what the user has entered. In that case, the method you are trying to use will become very complex and difficult to maintain, not to mention slow with a lot of data. The best bet is to build the filter in code. What I often do is name the controls the same as the field name and put the field type in the control's tag property. Then loop through the controls to build the filter. This is air code, but should give you a start. 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

这篇关于SQL包含条件(如果不为null)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！