A small bit of filtering VBA code help please

[smartcarfun]

There is 2 parts to this.... help with either appreciated!

I have the following 3 combo boxes to filter 3 columns of data;

Private Sub Combo116_AfterUpdate()
    If IsNull(Me.Combo116) Then
        Me.FilterOn = False
    Else
        Me.Filter = "BankLog = """ & Me.Combo116 & """"
        Me.FilterOn = True
    End If
End Sub
 
Private Sub Combo118_AfterUpdate()
    If IsNull(Me.Combo118) Then
        Me.FilterOn = False
    Else
        Me.Filter = "Team = """ & Me.Combo118 & """"
        Me.FilterOn = True
    End If
End Sub
 
Private Sub Combo154_AfterUpdate()
    If IsNull(Me.Combo154) Then
        Me.FilterOn = False
    Else
        Me.Filter = "Assigned = """ & Me.Combo154 & """"
        Me.FilterOn = True
    End If
End Sub
 

The first 2 work ok but the third gives me a Run-time error '3464': Data type mismatch in criteria expression.

Combo1 gets its selection from a list and Combo2&3 from 2 tables.

I cant see why combo 3 doesnt work?????

The second part is that they work independently of each other, ie when they update they don't consider the values in the other two combo boxes. How do I compile the code so updating from any combo box will consider all values when filtering.

In essence I just want to filter a continuous form with more than one combo box, such that blank combo boxes are not considered.

Thanks in adavance

[smartcarfun]

SOLVED for those who might need it.....

Private Sub Combo116_AfterUpdate()
    SetFilter
End Sub
 
Private Sub Combo118_AfterUpdate()
    SetFilter
End Sub
 
Private Sub Combo154_AfterUpdate()
    SetFilter
End Sub
 
Private Sub SetFilter()
    Dim strFilter As String
 
    If Not IsNull(Me.Combo116) Then
        strFilter = " AND BankLog = " & Chr(34) & Me.Combo116 & Chr(34)
    End If
    If Not IsNull(Me.Combo118) Then
        strFilter = strFilter & " AND Team = " & Chr(34) & Me.Combo118 & Chr(34)
    End If
    If Not IsNull(Me.Combo154) Then
        strFilter = strFilter & " AND Assigned = " & Me.Combo154
    End If
    If strFilter = "" Then
        Me.FilterOn = False
    Else
        Me.Filter = Mid(strFilter, 6)
        Me.FilterOn = True
    End If
End Sub

[NeoPa]

Good code. I would do it slightly differently in that I'd use the quote character in a string normally ("'") as I've never heard a good reason for using Chr() in such circumstances, even though examples of that abound. I see you've also realised that [Assigned] is not a string field so doesn't require the quotes at all.

The last bit, where the filter properties are actually set, I would do as :

    Me.Filter = Mid(strFilter, 6)
    Me.FilterOn = (Me.Filter > "")

You're code appears to be perfectly up to the job though :-)