I may have over simplified my problem. This is only a piece of my code. This is a form filter that filters about 15 different fields. Here is the complete code with the problem area in red.
Private Sub cmdApplyFilter_Click()
Dim strName As String
Dim strProjectUseFunction As String
Dim strFilter As String
Dim strYearCompleted As String
Dim strAddress As String
Dim strState As String
Dim strConstructionType As String
Dim strMallCampus As String
Dim strOwnerName As String
Dim strAgreementType As String
Dim strSelectionType As String
Dim strContractType As String
Dim strCity As String
Dim strCompletionAmount As String
Dim strSquareFoot As String
Dim strPM As String
Dim strSuper As String
Dim arrStr() As String
Dim arrStr1() As String
Dim arrStr2() As String
Dim arrStr3() As String
Dim arrStr4() As String
Dim arrStr5() As String
Dim arrStr6() As String
Dim arrStr7() As String
Dim arrStr8() As String
Dim intArrCnt As Integer
Dim intArrCnt1 As Integer
Dim intArrCnt2 As Integer
Dim intArrCnt3 As Integer
Dim intArrCnt4 As Integer
Dim intArrCnt5 As Integer
Dim intArrCnt6 As Integer
Dim intArrCnt7 As Integer
Dim intArrCnt8 As Integer
Dim strOutput As String
Dim strOutput1 As String
Dim strOutput2 As String
Dim strOutput3 As String
Dim strOutput4 As String
Dim strOutput5 As String
Dim strOutput6 As String
Dim strOutput7 As String
Dim strOutput8 As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Projects report") <> acObjStateOpen Then
DoCmd.OpenReport "Projects Report", acViewPreview
End If
' Build Name criteria string
If IsNull(Me.txtName.Value) Then
strName = "[name]Like '*'"
Else
arrStr = Split(txtName, ",")
intArrCnt = UBound(arrStr)
strOutput = "[Name]Like '*" & arrStr(0) & "*'"
For x = 1 To intArrCnt
strOutput = "([Name]Like '*" & arrStr(0) & "*'" & " Or [Name]Like '*" & arrStr(x) & "*')"
Next x
strName = strOutput
End If
' Build Address criteria string
If IsNull(Me.txtAddress.Value) Then
strAddress = ""
Else
arrStr1 = Split(txtAddress, ",")
intArrCnt1 = UBound(arrStr1)
strOutput1 = "AND[Address]Like '*" & arrStr1(0) & "*'"
For x = 1 To intArrCnt1
strOutput1 = "AND([Address]Like '*" & arrStr1(0) & "*'" & " Or [Address]Like '*" & arrStr1(x) & "*')"
Next x
strAddress = strOutput1
End If
' Build State criteria string
If IsNull(Me.txtState.Value) Then
strState = ""
Else
arrStr2 = Split(txtState, ",")
intArrCnt2 = UBound(arrStr2)
strOutput2 = "AND[State]Like '*" & arrStr2(0) & "*'"
For x = 1 To intArrCnt2
strOutput2 = "AND([State]Like '*" & arrStr2(0) & "*'" & " Or [State]Like '*" & arrStr2(x) & "*')"
Next x
strState = strOutput2
End If
' Build Mall/Campus criteria string
If IsNull(Me.txtMall.Value) Then
strMallCampus = ""
Else
arrStr3 = Split(txtMall, ",")
intArrCnt3 = UBound(arrStr3)
strOutput3 = "AND[Mall / Campus]Like '*" & arrStr3(0) & "*'"
For x = 1 To intArrCnt3
strOutput3 = "AND([Mall / Campus]Like '*" & arrStr3(0) & "*'" & " Or [Mall / Campus]Like '*" & arrStr3(x) & "*')"
Next x
strMallCampus = strOutput3
End If
' Build Owner Name criteria string
If IsNull(Me.txtOwnerName.Value) Then
strOwnerName = ""
Else
arrStr4 = Split(txtOwnerName, ",")
intArrCnt4 = UBound(arrStr4)
strOutput4 = "AND[Owner Name]Like '*" & arrStr4(0) & "*'"
For x = 1 To intArrCnt4
strOutput4 = "AND([Owner Name]Like '*" & arrStr4(0) & "*'" & " Or [Owner Name]Like '*" & arrStr4(x) & "*')"
Next x
strOwnerName = strOutput4
End If
' Build City criteria string
If IsNull(Me.txtCity.Value) Then
strCity = ""
Else
arrStr5 = Split(txtCity, ",")
intArrCnt5 = UBound(arrStr5)
strOutput5 = "AND[City]Like '*" & arrStr5(0) & "*'"
For x = 1 To intArrCnt5
strOutput5 = "AND([City]Like '*" & arrStr5(0) & "*'" & " Or [City]Like '*" & arrStr5(x) & "*')"
Next x
strCity = strOutput5
End If
' Build Use / Function criteria string
If IsNull(Me.txtProjectUseFunction.Value) Then
strProjectUseFunction = ""
Else
arrStr6 = Split(txtProjectUseFunction, ",")
intArrCnt6 = UBound(arrStr6)
strOutput6 = "AND[Project Use / Function]Like '*" & arrStr6(0) & "*'"
For x = 1 To intArrCnt5
strOutput6 = "AND([Project Use / Function]Like '*" & arrStr6(0) & "*'" & " Or [Project Use / Function]Like '*" & arrStr6(x) & "*')"
Next x
strProjectUseFunction = strOutput6
End If
' Build Project Manager criteria string
If IsNull(Me.txtPM.Value) Then
strPM = ""
Else
arrStr7 = Split(txtPM, ",")
intArrCnt7 = UBound(arrStr7)
strOutput7 = "AND[Project Manager]Like '*" & arrStr7(0) & "*'"
For x = 1 To intArrCnt7
strOutput7 = "AND([Project Manager]Like '*" & arrStr7(0) & "*'" & " Or [Project Manager]Like '*" & arrStr7(x) & "*')"
Next x
strPM = strOutput7
End If
' Build Superintendent criteria string
If IsNull(Me.txtSuper.Value) Then
strSuper = ""
Else
arrStr8 = Split(txtSuper, ",")
intArrCnt8 = UBound(arrStr8)
strOutput8 = "AND[Superintendent]Like '*" & arrStr8(0) & "*'"
For x = 1 To intArrCnt8
strOutput8 = "AND([Superintendent]Like '*" & arrStr8(0) & "*'" & " Or [Superintendent]Like '*" & arrStr8(x) & "*')"
Next x
strSuper = strOutput8
End If
' Build Year Completed >
If IsNull(Me.txtYearCompleted.Value) Then
strYearCompleted = ""
Else
strYearCompleted = "AND [Year Completed]> '" & Me.txtYearCompleted.Value & "'"
End If
' Build Completion Amount >
If IsNull(Me.txtAmount.Value) Then
strCompletionAmount = ""
Else
strCompletionAmount = "AND [Final Contract]> '" & Me.txtAmount.Value & "'"
End If
' Build Project Square Foot >
If IsNull(Me.txtSF.Value) Then
strSquareFoot = ""
Else
strSquareFoot = "AND [Square Feet]> '" & Me.txtSF.Value & "'"
End If
' Build criteria string from lstConstruction listbox
For Each varItem In Me.lstConstruction.ItemsSelected
strConstructinType = strConstructionType & ",'" & Me.lstConstruction.ItemData(varItem) _
& "'"
Next varItem
If Len(strConstructionType) = 0 Then
strConstructionType = ""
Else
strConstructionType = Right(strConstructionType, Len(strConstructionType) - 1)
strConstructionType = "AND [Construction Type]IN(" & strConstructionType & ")"
End If
' Build criteria string from lstAgreement listbox
For Each varItem In Me.lstAgreement.ItemsSelected
strAgreementType = strAgreementType & ",'" & Me.lstAgreement.ItemData(varItem) _
& "'"
Next varItem
If Len(strAgreementType) = 0 Then
strAgreementType = ""
Else
strAgreementType = Right(strAgreementType, Len(strAgreementType) - 1)
strAgreementType = "AND [Type of Agreement]IN(" & strAgreementType & ")"
End If
' Build criteria string from lstSelection listbox
For Each varItem In Me.lstSelection.ItemsSelected
strSelectionType = strSelectionType & ",'" & Me.lstSelection.ItemData(varItem) _
& "'"
Next varItem
If Len(strSelectionType) = 0 Then
strSelectionType = ""
Else
strSelectionType = Right(strSelectionType, Len(strSelectionType) - 1)
strSelectionType = "AND [Selection Process]IN(" & strSelectionType & ")"
End If
' Build criteria string from lstContract listbox
For Each varItem In Me.lstContract.ItemsSelected
strContractType = strContractType & ",'" & Me.lstContract.ItemData(varItem) _
& "'"
Next varItem
If Len(strContractType) = 0 Then
strContractType = ""
Else
strContractType = Right(strContractType, Len(strContractType) - 1)
strContractType = "AND [Contract Type]IN(" & strContractType & ")"
End If
' Build filter string
strFilter = strName & strAddress & strState & strMallCampus & strOwnerName & strCity & strProjectUseFunction & strPM & strSuper & _
strYearCompleted & strCompletionAmount & strSquareFoot & strConstructionType & strAgreementType & strSelectionType & _
strContractType
' Apply filter to report
With Reports![Projects Report]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
Reports![Projects Report].FilterOn = False
End Sub