时间戳

首页 > 解决方案 > MS Access 过滤问题

vba - MS Access 过滤问题

问题描述

我在基于组合框自动过滤报告/表单时遇到问题。

这个特定的行：

Form_sfrmSpending.Filter = strAT & " AND " & strBur & " AND " & strFT

如何修复此语法？

谢谢你

Dim strBur As String
Dim strAT As String
Dim strFT As String

strBur = "Bureau='" & Me.cboBur.Value & "'"
strAT = "Categories'" & Me.cboCategories.Value & "'"
strFT = "[Funding Type]'" & Me.cboFunding.Value & "'"


If cboBur = "All Bureaus" Then
If cboCategories = "All Categories" Then
If cboFunding = "All Funding Type" Then
Form_sfrmSpending.Filter = ""
Form_sfrnSpending.FilterOn = False
End If
End If
End If


If cboBur <> "All Bureaus" Then
If cboCategories = "All Categories" Then
If cboFunding = "All Funding Type" Then
Form_sfrmSpending.Filter = strBur
 Form_sfrmSpending.FilterOn = True
End If
End If
End If

If Me.cboCategories <> "All Categories" Then
If cboBur = "All Bureaus" Then
If cboFunding = "All Funding Type" Then
Form_sfrmSpending.Filter = strAT
Form_sfrmSpending.FilterOn = True
End If
End If
End If

If Me.cboFunding <> "All Funding Type" Then
If cboBur = "All Bureaus" Then
If cboCategories = "All Categories" Then
Form_sfrmSpending.Filter = strFT
Form_sfrmSpending.FilterOn = True
End If
End If
End If

If Me.cboCategories <> "All Action Types" Then
If cboBur <> "All Bureaus" Then
If cboFunding <> "All Funding Type" Then

Form_sfrmSpending.Filter = strAT & " AND " & strBur & " AND " & strFT
Form_sfrmSpending.FilterOn = True
Debug.Print Form_sfrmSpending.Filter
End If
End If
End If

我添加了完整的代码以防万一。

标签： vbams-access

解决方案

您缺少=两个代码行中的符号。

你在这里：

strBur = "Bureau='" & Me.cboBur.Value & "'"
                ^

但它在这里丢失，造成语法错误：

strAT = "Categories '" & Me.cboCategories.Value & "'"
                   ^
strFT = "[Funding Type] '" & Me.cboFunding.Value & "'"
                       ^

另外，你的逻辑太复杂了。If您可以为每个组合框构建一个过滤器字符串：

Dim sFilter As String
sFilter = ""

If cboBur <> "All Bureaus" Then
    sFilter = StrAppend(sFilter, strBur, " AND ")
End If
If cboCategories <> "All Categories" Then
    sFilter = StrAppend(sFilter, strAT, " AND ")
End If
' etc.

If sFilter <> "" Then
    Form_sfrmSpending.Filter = sFilter
    Form_sfrmSpending.FilterOn = True
End If

仅当两个字符串都不为空时才使用包含分隔符的此辅助函数：

Public Function StrAppend(sBase As String, sAppend As Variant, sSeparator As String) As String

    If Len(sAppend) > 0 Then
        If sBase = "" Then
            StrAppend = Nz(sAppend, "")
        Else
            StrAppend = sBase & sSeparator & Nz(sAppend, "")
        End If
    Else
        StrAppend = sBase
    End If

End Function

推荐阅读