Report Filter Code Not Working

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Report Filter Code Not Working

Post by EnginerdUNH »

I have a report that runs fine when you don't try to apply any filters to it and displays all of the records that it should display. The query behind the report is as follows:

Code: Select all

SELECT tblInspectionInfo.InspectionID, tblInspectionInfo.SupplierID, tblInspectionInfo.TubeSerialID, tblInspectionInfo.InspectionTypeID, tblInspectionInfo.OtherDescription, tblInspectionInfo.DataSubmitBy, tblInspectionInfo.DataSubmitDate, tblInspectionInfo.DataReviewBy, tblInspectionInfo.DataReviewDate, tblInspectionInfo.DataCheckBy, tblInspectionInfo.DataCheckDate, tblInspectionInfo.AnalysisCompleteBy, tblInspectionInfo.AnalysisCompleteDate, tblInspectionInfo.AnalysisCheckBy, tblInspectionInfo.AnalysisCheckDate, tblInspectionInfo.Status135, tblInspectionInfo.Status225, tblInspectionInfo.Status315, tblInspectionInfo.DataMoved, tblInspectionInfo.InspectionNotes
FROM tblInspectionInfo
WHERE (((tblInspectionInfo.SupplierID) Is Not Null) AND ((tblInspectionInfo.TubeSerialID) Is Not Null) AND ((tblInspectionInfo.InspectionTypeID) Is Not Null));
I have written the following code which is supposed to set the WhereCondition of the fields which the database user can filter the report by to show only the data they want. The problem that I'm running into is that when I attempt to use the code is Access throws a "data type mismatch in criteria expression" error.

Code: Select all

Private Sub cmdRunReport_Click()
    'tells database how to filter (or not filter) the report to display the desired inspection data
    Dim strWhere As String
    On Error GoTo ErrHandler
    If Not IsNull(Me.cmbSupplier) Then
        strWhere = " AND SupplierID='" & Me.cmbSupplier & "'"
    End If
    If Not IsNull(Me.cmbTubeSerial) Then
        strWhere = strWhere & " AND TubeSerialID='" & Me.cmbTubeSerial & "'"
    End If
    If Not IsNull(Me.cmbInspectionType) Then
        strWhere = strWhere & " AND InspectionTypeID='" & Me.cmbInspectionType & "'"
    End If
    If Not IsNull(Me.cmbAcceptability) Then
        strWhere = strWhere & " AND Status135='" & Me.cmbAcceptability & "'" & " AND Status225='" & Me.cmbAcceptability & "' AND Status315='" & Me.cmbAcceptability & "'"
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport "rptAllInspections", View:=acViewPreview, WhereCondition:=strWhere
    Me.Visible = False
    Exit Sub
ErrHandler:
    If Err <> 2501 Then
        MsgBox Err.Description, vbExclamation
    End If
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Report Filter Code Not Working

Post by HansV »

Assuming that SupplierID, TubeSerialID and InspectionTypeID are number fields, you should not enclose the value of the corresponding combo boxes in (single) quotes:

Code: Select all

Private Sub cmdRunReport_Click()
    'tells database how to filter (or not filter) the report to display the desired inspection data
    Dim strWhere As String
    On Error GoTo ErrHandler
    If Not IsNull(Me.cmbSupplier) Then
        strWhere = " AND SupplierID=" & Me.cmbSupplier
    End If
    If Not IsNull(Me.cmbTubeSerial) Then
        strWhere = strWhere & " AND TubeSerialID=" & Me.cmbTubeSerial
    End If
    If Not IsNull(Me.cmbInspectionType) Then
        strWhere = strWhere & " AND InspectionTypeID=" & Me.cmbInspectionType
    End If
    If Not IsNull(Me.cmbAcceptability) Then
        strWhere = strWhere & " AND Status135='" & Me.cmbAcceptability & "'" & " AND Status225='" & Me.cmbAcceptability & "' AND Status315='" & Me.cmbAcceptability & "'"
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport "rptAllInspections", View:=acViewPreview, WhereCondition:=strWhere
    Me.Visible = False
    Exit Sub
ErrHandler:
    If Err <> 2501 Then
        MsgBox Err.Description, vbExclamation
    End If
End Sub
If the status fields are number fields too, you should remove the single quotes there too.
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Report Filter Code Not Working

Post by EnginerdUNH »

Thank you Hans! That did the trick!