Filter Question

Huey462

Board Regular
Joined
Jul 25, 2011
Messages
147
I have 2 fields that I would like to use to have the records displayed to only be those that match the criteria. Individually the filter works but when I try to combine them I get a Syntax error for a missing operator.

VBA Code:
Private Sub ApplyFilter()

'Entire Filter String
Dim FltCriteria As String
'Individual Section Strings
Dim FilterNo1Check As String
Dim FilterNo2Check As String

Forms!frmPhoneListing.FilterOn = False
FltCriteria = ""

If Me!FilterNo1 <> "" Then
    FilterNo1Check = Me!FilterNo1
    FltCriteria = " [PhContactMain]LIKE " & "'" & "*" & FilterNo1Check & "*" & "'"
End If

If Me!FilterNo2 <> "" Then
    FilterNo2Check = Me!FilterNo2
    If FltCriteria = "" Then
        FltCriteria = " [PhContactSub]LIKE " & "'" & "*" & FilterNo2Check & "*" & "'"
        Else
        FltCriteria = FltCriteria & " [PhContactSub]LIKE " & "'" & "*" & FilterNo2Check & "*" & "'"
    End If
End If

Forms!frmPhoneListing.Filter = FltCriteria
Forms!frmPhoneListing.FilterOn = True
End Sub
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
See if this works
VBA Code:
FltCriteria = FltCriteria & " AND [PhContactSub]LIKE " & "'" & "*" & FilterNo2Check & "*" & "'"
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,803
Not wrong, but you can save yourself some typing in the future by not concatenating every wildcard or single quote character, plus less chance of messing up maybe:
LIKE " & "'" & "*" & FilterNo1Check & "*" & "'"
vs
LIKE '*" & FilterNo2Check & "*'"
 

Forum statistics

Threads
1,089,218
Messages
5,406,923
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top