Filter Report with a Form

[Closemofo]

Hello,

I have this code which allows me filter a report.
But I can use only one combo box at the same time.
I would like to be able to filter by using both combo box.

I would like by after adding a text box which search on a field
AutoNumber.

Thank you in advance for your answers.

Yves

-----------------------------------------------------
Private Sub cmdOpenReport_Click()

Dim strWhere As String
Dim strReport As String

strReport = "rpt Operator"

If Not IsNull(Me.Operator) Then
strWhere = strWhere & "[Operator] = '" & Me![cmbOperator].Value & "'"
End If

If Not IsNull(Me.cmbOperatorCity) Then
strWhere = strWhere & "[Operator City] = '" &
Me![cmbOperatorCity].Value & "'"
End If

DoCmd.Close
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Exit Sub

GestionErr:
If Err.Number = 2501 Then Exit Sub
MsgBox "Erreur : " & Err.Number & " " & Err.Description,
vbCritical, "Report Filter"
Exit Sub
End Sub

 

[Allen Browne]

You need to end up with a WhereCondition string that looks like this:
"([Operator] = ""Jim"") AND ([Operator City] = ""Springfield"")"

What we do tag the " AND " onto each one as we go, and then take off the
trailing one at the end. That logic makes it really simple to add further
combos for filtering as well. Something like this:

Private Sub cmdOpenReport_Click()
Dim strWhere As String
Dim strReport As String
Dim lngLen As Long

strReport = "rptOperator"

If Not IsNull(Me.Operator) Then
strWhere = strWhere & "([Operator] = """ & _
Me![cmbOperator].Value & """) AND "
End If

If Not IsNull(Me.cmbOperatorCity) Then
strWhere = strWhere & "([Operator City] = """ & _
Me![cmbOperatorCity].Value & """) AND "
End If
'etc

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = Left$(strWhere, 5)
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Close acForm, Me.Name
Exit Sub

GestionErr:
If Err.Number <> 2501 Then
MsgBox "Erreur : " & Err.Number & " " & Err.Description, _
vbCritical, "Report Filter"
End If
Exit Sub
End Sub