Subform Filter based on Multiple Comboboxes

[jlundbe2]

Hey!

So, I am having an issue getting a sub form to filter based on selections in a few combo boxes. The issue I run into is when trying to sort by a defined value for "year" and one of the multiple items of status, i.e. "<All Open>". The filter for "<All Open>" returns the incorrect values. The code is below. After looking online for a few days I am completely stumped - maybe you all could help a state sider?

Thanks!

Public Sub FormFilter()
'-----------------------------------------------------------------
'Purpose: Filters search subform frmDeals_Audits_Select_tv
'
'CalledFrom: cboAudit_Yr_Change, cboAuditor_Change, cboStatus_Change
'Calls: basErrorHandler.ErrorHandler()
'Assumes: na
'Returns: na
'------------------------------------------------------------------
'
Dim iYear As Integer
Dim sAuditor As String
Dim sCategory As String
Dim sFilter As String
Dim sStatus As String
Dim sJVAct As String
Dim sVertical As String
Dim sBusiness As String

On Error GoTo ErrorHandling:

    'Whats the Vertical filter set to
    If Me.cboVertical.Value = "<All>" Then 'All
        sVertical = ""
    Else
        sVertical = " Vertical = '" & Me.cboVertical.Value & "'"
    End If
    
    'Whats the Business filter set to
    If Me.cboBusiness.Value = "<All>" Then 'All
        sBusiness = ""
    Else
        sBusiness = " Business = '" & Me.cboBusiness.Value & "'"
    End If
    
    'Whats the JV Accountant filter set to
    If Me.cboJVAct.Value = "<All>" Then  'All
        sJVAct = ""
    Else
        sJVAct = " Accountant = '" & Me.cboJVAct.Value & "'"
    End If

    'Whats the Audit_Yr filter set to
    If Me.cboAudit_Yr.Value = "<All>" Then  'All
        sYear = ""
    Else
        sYear = "Audit_Yr = " & Me.cboAudit_Yr.Value
    End If
    
    'Whats the Auditor filter set to
    If Me.cboAuditor.Value = "<All>" Then
        sAuditor = ""
    Else
        sAuditor = "Auditor = '" & Me.cboAuditor.Value & "'"
    End If
    
    'Whats the Audit Status filter set to
    Select Case Me.cboStatus.Value
      Case "<All >"
        sStatus = ""
      Case "<All Closed>"
        sStatus = "Audit_Status = 'Issued' OR Audit_Status = 'Approved' OR Audit_Status = 'Next Audit' OR Audit_Status = 'Waived' OR Audit_Status = 'Out of Scope' OR Audit_Status = 'Sold'"
      Case "<All Open>"
        sStatus = "Audit_Status = 'Draft' OR Audit_Status = 'Review' OR Audit_Status = 'In Process' OR Audit_Status = 'Not Started'"
      Case "Approved"
        sStatus = "Audit_Status = 'Approved'"
      Case "Draft"
        sStatus = "Audit_Status = 'Draft'"
      Case "Out of Scope"
        sStatus = "Audit_Status = 'Out of Scope'"
      Case "In Process"
        sStatus = "Audit_Status = 'In Process'"
      Case "Issued"
        sStatus = "Audit_Status = 'Issued'"
      Case "Next Audit"
        sStatus = "Audit_Status = 'Next Audit'"
      Case "Not Started"
        sStatus = "Audit_Status = 'Not Started'"
      Case "Waived"
        sStatus = "Audit_Status = 'Waived'"
      Case "Sold"
        sStatus = "Audit_Status = 'Sold'"
    End Select
    
    'Build filter string
    If sVertical <> "" Then
        If sFilter = "" Then
            sFilter = sVertical
        Else
            sFilter = sFilter & " and " & sVertical
        End If
    End If

    If sBusiness <> "" Then
        If sFilter = "" Then
            sFilter = sBusiness
        Else
            sFilter = sFilter & " and " & sBusiness
        End If
    End If
    
    If sJVAct <> "" Then
        If sFilter = "" Then
            sFilter = sJVAct
        Else
            sFilter = sFilter & " and " & sJVAct
        End If
    End If
    
    If sYear <> "" Then
        If sFilter = "" Then
            sFilter = sYear
        Else
            sFilter = sFilter & " and " & sYear
        End If
    End If
        
    If sAuditor <> "" Then
        If sFilter = "" Then
            sFilter = sAuditor
        Else
            sFilter = sFilter & " and " & sAuditor
        End If
    End If
    
    If sStatus <> "" Then
          If sFilter = "" Then
          sFilter = sStatus
          Else
          sFilter = sFilter & " and " & sStatus
          End If
    End If
    
    Me.frmDeals_Audits_Select_tv.Form.Filter = sFilter
        
    If sFilter = "" Then
      Me.frmDeals_Audits_Select_tv.Form.FilterOn = False
    Else
      Me.frmDeals_Audits_Select_tv.Form.FilterOn = True
    End If
    
    
ExitProc:
    Exit Sub

ErrorHandling:
    If ErrorHandler("frmDeals_Tab.FormFilter", Err.Number) Then
        Resume Next
    Else
        GoTo ExitProc
    End If
End Sub

 

[namliam]

Just a quick gamble

Your OR vs AND is the problem, you need to enclose your sStatus with ().
ie.
sStatus = "( Audit_Status = 'Draft' OR Audit_Status = 'Review' OR Audit_Status = 'In Process' OR Audit_Status = 'Not Started' ) "

Also please use [ code ] and [/ code ], without the spaces, around your code when posting code. This is unreadable!

P.s. Using these different parts of where clauses works, but why not do it directly??
i.e. Use sFilter directly like so:
sFilter = "AND Auditor = '" & Me.cboAuditor.Value & "'"

 

[jlundbe2]

Mailman,

Thanks for the prompt reply. I have to run off to a quick meeting but wanted to let you know that your solution for the status worked.

I will take a look at the where clauses this afternoon!

Jason

Thanks for the code suggestion as well. I am new to the forum.