Warm tip: This article is reproduced from stackoverflow.com, please click
ms-access vba

MS Access filtering issue

发布于 2020-04-07 23:19:40

I am having an issue with automatically filtering a report/form based on a combo box.

This particular line:

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

How do I fix this syntax?

Thank You

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

I added the full code for any confusion.

Questioner
Tyrone
Viewed
35
分享
Andre 2020-02-01 20:17

You're missing the = sign in two code lines.

You have it here:

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

but it's missing here, creating syntax errors:

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

Also, your logic is too complicated. You can build the filter string with one single If for each combo box:

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

using this helper function that includes the Separator only if both strings aren't empty:

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

Related issues

1
How to sort tables across multiple worksheets?
2
Find matching value row id from one workbook to another workbook
3
Delete empty rows in tables across multiple worksheets
4
How to find Number of Occurences of Slash from a strings
5
Issues with Application.Onkey
6
Converting formulas in certain column for specific worksheet
7
VBA looping through sheets removing duplicates
8
How do I set font for text but keep font in equations in Word with VBA?
9
VBA function runs as a macro but gives error when called with function
10
How do I change decimal dot into decimal comma?