Filtering a subform with a combo box

[sal]

I have been filtering a subform with two combo boxes without any troube but when I tried to add a third I started getting errors. Here is the code I have:

Private Sub RunFilter()

Dim strFilter As String
Dim bFilter As Boolean

bFilter = False
strFilter = ""


If Nz(Me.ComboFilterSpp, 0) > 0 Then 'Period
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Species = " & Me.ComboFilterSpp
bFilter = True
End If


If Nz(Me.ComboFilterYear, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "RunYear = " & Me.ComboFilterYear
bFilter = True
End If

If Nz(Me.ComboFilterSubmitter, 0) > 0 Then 'TargetType
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "Data_Submitter_Last_Name = " & Me.ComboFilterSubmitter
bFilter = True
End If

If bFilter Then
Me.Survey_Report_Query_subform.Form.OrderBy = ""
Me.Survey_Report_Query_subform.Form.Filter = strFilter
Me.Survey_Report_Query_subform.Form.FilterOn = True
Else
Me.Survey_Report_Query_subform.Form.FilterOn = False
End If

End Sub

The ComboFilterSubmitter condition has caused no records to be returned.

Thank you in advance...

 

[pbaldy]

Two comments. First, fields with a text data type need the value surrounded by single quotes:

strFilter = strFilter & "Data_Submitter_Last_Name = '" & Me.ComboFilterSubmitter & "'"

Also, your filter will have a trailing " AND " which you need to trim off.