Simplifying multiple filter code (1 Viewer)

[Nevsky78]

Hi all,

I am a very basic VBA user who has filled out some code for a continuous form multiple filter.

The code is working quite happily.

However, and this is more for my education, I'm sure the coding could be smaller and more efficient, but I don't know how!

So I was wondering if anyone might be able to help me minimize the coding in my sub please?

Private Sub DoFilter()
If IsNull(Me.cboPeriodSelect) And IsNull(Me.cboCategorySelect) Then
Me.Filter = "bcr_b_ir =" & cboBrandSelect
Me.FilterOn = True
Me.Refresh
Else
    If IsNull(Me.cboBrandSelect) And IsNull(Me.cboCategorySelect) Then
    Me.Filter = "bcr_np_ir =" & cboPeriodSelect
    Me.FilterOn = True
    Me.Refresh
    Else
        If IsNull(Me.cboBrandSelect) And IsNull(Me.cboPeriodSelect) Then
        Me.Filter = "bcr_sc_ir =" & cboCategorySelect
        Me.FilterOn = True
        Me.Refresh
        Else
            If IsNull(Me.cboBrandSelect) Then
            Me.Filter = "bcr_sc_ir =" & cboCategorySelect & "AND bcr_np_ir =" & cboPeriodSelect
            Me.FilterOn = True
            Me.Refresh
            Else
                If IsNull(Me.cboPeriodSelect) Then
                Me.Filter = "bcr_sc_ir =" & cboCategorySelect & "AND bcr_b_ir =" & cboBrandSelect
                Me.FilterOn = True
                Me.Refresh
                Else
                    If IsNull(Me.cboCategorySelect) Then
                    Me.Filter = "bcr_np_ir =" & cboPeriodSelect & "AND bcr_b_ir =" & cboBrandSelect
                    Me.FilterOn = True
                    Me.Refresh
                    Else
                        Me.Filter = "bcr_np_ir =" & cboPeriodSelect & "AND bcr_b_ir =" & cboBrandSelect & "AND bcr_sc_ir =" & cboCategorySelect
                        Me.FilterOn = True
                        Me.Refresh
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

Many thanks in advance,
Nick

 

[namliam]

Notice the SPACE before each AND you NEED this in order to ensure things work

    Dim FilterText As String
    FilterText = " 1=1 "
    If Not IsNull(cboPeriodSelect) Then FilterText = FilterText & " AND bcr_np_ir =" & cboPeriodSelect
    If Not IsNull(cboBrandSelect) Then FilterText = FilterText & " AND bcr_b_ir =" & cboBrandSelect
    If Not IsNull(cboCategorySelect) Then FilterText = FilterText & " AND bcr_sc_ir =" & cboCategorySelect
    Me.Filter = FilterText
    Me.FilterOn = True
    Me.Refresh

Each of these combo's better return a number value otherwize it will cause problems too

 

[Nevsky78]

namliam,

That is absolutely fantastic, thank you so much! Way, way cleaner.

Thank you!
Nick

 

[CJ_London]

Try

Private Sub DoFilter()
    Me.Filter = "bcr_np_ir =" & nz(cboPeriodSelect) & " OR " & nz(cboPeriodSelect,0) & " = 0 AND bcr_b_ir =" & nz(cboBrandSelect) & " OR " & nz(cboBrandSelect,0) & " = 0 AND bcr_sc_ir =" & nz(cboCategorySelect) & " OR " 
& nz(cboCategorySelect,0) & " = 0"
    me.filteron=true
End Sub