Vba Filter Fail

[ErickMJ]

So I have a report with a lot of fields that the user needs to be able to filter from a form full of combo boxes and text boxes. I've made code that I think should work but isn't.

DoCmd.OpenReport "Custom Report", acViewReport, , whereString

If Not IsNull(Me.txtID) Then
whereCriteria = (whereCriteria & " AND ") & "[Project Master Table].[Project ID] = " & Me.txtID
End If

Only there are like a dozen more statements like that and one nested Iif statement (also not working

[Project Master Table].[Rework Needed] = IIf(Me.cmbRework = "Yes", -1, IIf(Me.cmbRework = "No", 0, ""))

for anyone who wants to take a crack at it).
It opens the report just fine, but doesn't seem to filter anything. What am I doing wrong?

 

[boblarson]

Post the entire thing. If you want to see one of mine, here you go:

Private Sub cmdOK_Click()
    Dim strWhere As String
    Dim strHold As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim lngRec As Long
    Dim strCheckBoxSelections As String
    Dim strSQLParameter As String
    Dim blnNoOrderBy As Boolean
    Dim strOrderBy As String
    Dim strPropertyIDs As String
 
    On Error GoTo cmdOK_Click_Error
 
     Set db = CurrentDb
    RemoveQueryWhereClause "mktblPipeRpt"
    RemoveQueryWhereClause "qrymktblPipeRpt"
 
    ' Originator Selections
    If AreThereListSelections(Me.lstOriginator) Then
        If Me.lstOriginator.ItemsSelected.Count <> Me.lstOriginator.ListCount Then
            strHold = "OrigID In(" & GetListBoxValues(Me.lstOriginator, 1) & ") AND "
        End If
    End If
    ' LT Name Selections
    If AreThereListSelections(Me.lstLTNames) Then
        If Me.lstLTNames.ItemsSelected.Count <> Me.lstLTNames.ListCount Then
            strHold = strHold & "property_id In(" & GetListBoxValues(Me.lstLTNames, 1) & ") AND "
        End If
    End If
 
    ' Developer Selections
    If AreThereListSelections(Me.lstDevelopers) Then
        strHold = strHold & "DevID In(" & GetListBoxValues(Me.lstDevelopers, 1) & ") AND "
    End If
 
    ' Timing Types Selections
    If AreThereListSelections(Me.lstTimingTypes) Then
        strHold = strHold & "TimingTypeFK In(" & GetListBoxValues(Me.lstTimingTypes, 1) & ") AND "
    End If
 
    ' State Selections
    If AreThereListSelections(Me.lstTimingTypes) Then
        strHold = strHold & "TimingTypeFK In(" & GetListBoxValues(Me.lstTimingTypes, 1) & ") AND "
    End If
 
    ' County Selections
    If AreThereListSelections(Me.lstCounties) Then
        strHold = strHold & "county In(" & GetListBoxValues(Me.lstCounties, 2) & ") AND "
    End If
 
    ' City Selections
    If AreThereListSelections(Me.lstCities) Then
        strHold = strHold & "Property_City In(" & GetListBoxValues(Me.lstCities, 2) & ") AND "
    End If
 
    ' TC Status Selections
    If AreThereListSelections(Me.lstTCSTatus) Then
        strHold = strHold & "TCStatus In(" & GetListBoxValues(Me.lstTCSTatus, 1) & ") AND "
    End If
 
    ' Developer Type Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllDeveloperTypes", 3, Chr(34), "RepeatDeveloperOrNew")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "RepeatDeveloperOrNew" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Probability Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllProbability", 3, Chr(34), "Probability")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "Probability" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Key Transaction Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllKeyTrans", 3, vbNullString, "KeyTrans")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "KeyTrans" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Property Type Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllPropTypes", 3, Chr(34), "Property_Type")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "Property_Type" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Tenant Mix Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllTenantMix", 3, Chr(34), "Tenant_Mix")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "Tenant_Mix" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Market Type Selections
    strCheckBoxSelections = chkPipelineBoxSelections(Me, "cmdSelAllMktTypes", 3, Chr(34), "Market_Type")
    If strCheckBoxSelections <> vbNullString And Trim(strCheckBoxSelections) <> "Market_Type" Then
        strHold = strHold & " " & strCheckBoxSelections & " AND "
    End If
 
    ' Units Between
    If Len(Me.txtUnitsFrom & vbNullString) > 0 And Len(Me.txtUnitsTo & vbNullString) > 0 Then
        strHold = strHold & " [Units] Between " & Me.txtUnitsFrom & " And " & Me.txtUnitsTo & " AND "
    End If
 
    ' PropertyID's including subform ones
    strPropertyIDs = GetPropIDsForSubReportItems
 
    If Len(Forms!frmReports.cboUpperTier & vbNullString) > 0 Then
        If Len(strPropertyIDs & vbNullString) > 0 Then
            strWhere = "[dbo_pselPropertyNarrow].[Property_ID] In(" & strPropertyIDs & "," & RetUTPIDs(Forms!frmReports.cboUpperTier) & ")"
        Else
            strWhere = "[dbo_pselPropertyNarrow].[Property_ID] In(" & RetUTPIDs(Forms!frmReports.cboUpperTier) & ")"
        End If
        If strWhere <> vbNullString Then
            Set qdf = db.QueryDefs("mktblPipeRpt")
        End If
    Else
        If Len(strPropertyIDs & vbNullString) > 0 Then
            strWhere = "[Property_ID] In(" & strPropertyIDs & ")"
            Set qdf = db.QueryDefs("qrymktblPipeRpt")
        Else
            strWhere = vbNullString
        End If
        If strWhere <> vbNullString Then
        End If
    End If
 
    If strWhere <> vbNullString Then
        qdf.SQL = ReplaceWhereClause(qdf.SQL, "WHERE " & strWhere)
        qdf.Close
    End If
 
    ' Open the report
    If Right(strHold, 5) = " AND " Then
        strHold = Left(strHold, Len(strHold) - 5)
    End If
    strWhere = strHold
 
    DoCmd.OpenReport "rptPipeline", acViewPreview, , strWhere, OpenArgs:=GetPipeRptSortOrder
 
cmdOK_Click_Exit:
    Exit Sub
cmdOK_Click_Error:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOK_Click of VBA Document Form_frmPipelineCriteriaAndSort"
    End If
    Resume cmdOK_Click_Exit
    Resume
End Sub