在访问中,我一直在尝试对显示在检查列表的子表单中的内容设置用户过滤器。我尝试研究的其他方法没有奏效,但我使用以下代码取得了成功,如果用户填写任何其他过滤器选项,它会进行过滤。我能想出的是如何让它接受多个过滤器,除非我拼出所有可能的框组合
那么无论如何这实际上是可能的还是我需要查看其他选项?
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])
) )
);
如果我理解正确,您有一个包含多个未绑定控件的表单,用户将填写这些控件。然后您想根据用户输入的内容过滤您的子表单。在这种情况下,您尝试使用的方法将变得非常复杂且难以维护,更不用说在大量数据的情况下速度很慢。最好的办法是在代码中构建过滤器。我经常做的是将控件命名为与字段名称相同的名称,并将字段类型放在控件的标签属性中。然后遍历控件以构建过滤器。这是空气代码,但应该给你一个开始。 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句