Multiple Criteria for Where Condition for OpenReport

[koolwaters]

I have a report where I need to sometimes specify multiple criteria to limit the results of the report. I created a form with the 2 combo boxes where I can choose the desired criteria.

In essence I may need to filter my results based on the Status and the Level.

So I want the where clause to be Status =ctlStatus and Level =ctlLevel but I am not sure how to enter it.

 Private Sub cmdOpenReport_Click()
On Error GoTo Error_Handler
DoCmd.OpenReport "Opened Items", acViewPreview, , "[Status]='" & Me.ctlStatus & "'"
Error_Handler:
DoCmd.CancelEvent
End Sub

Thanks for any help!

 

[boblarson]

DoCmd.OpenReport "Opened Items", acViewPreview, , "[Status]='" & Me.ctlStatus & "' And  [Level]='" & Me.ctlLevel & "'"

 

[koolwaters]

I'm sorry Bob. I was not as detailed as I should have been.

I need to be able to view the report if nothing is entered in either of the combo boes and also if information is in either one of them. So in essence, I may want to run the report and view all of the information. I may only want to see records with a priority Status or Level III. I may also want to see records with both Priority Status and Level III.

When I modify my code to match your suggestion, the report only opens if there is something in both combo boxes.

I hope you can understand what I am trying to achieve.

Thanks for your help.

 

[boblarson]

Then modify to this:

Dim strWhere As String
 
If Len(Me.ctlStatus & "") > 0 Then
   strWhere = "[Status]='" & Me.ctlStatus & "' 
End If
 
If strWhere <> "" Then
  If Len(Me.ctlLevel & "") <> 0 Then
      strWhere = "[Status]='" & Me.ctlStatus & "' And  [Level]='" & Me.ctlLevel & "'"
  Else
      strWhere = [Level]='" & Me.ctlLevel & "'"
  End If
End If
DoCmd.OpenReport "Opened Items", acViewPreview, , strWhere

<!-- / message --><!-- sig -->

 

[koolwaters]

A little problem.

When I leave the code as is the report runs when there is no criteria and when there is both a Status and a Level. However, it does not run when there is just a Status or just a Level.

 

[koolwaters]

Hi Bob! I have played around with the code you posted and it seems to be working correctly.

Here is the code I used.

Private Sub cmdOpenReport_Click()
On Error GoTo Error_Handler

Dim strWhere As String
 
If Len(Me.ctlStatus & "") > 0 Then
strWhere = "[Status]='" & Me.ctlStatus & "' "
End If

If Len(Me.ctlLevel & "") > 0 Then
strWhere = "[Level]='" & Me.ctlLevel & "' "
End If

If strWhere <> "" Then
If Len(Me.ctlLevel & "") <> 0 Then
If Len(Me.ctlStatus & "") <> 0 Then
strWhere = "[Status]='" & Me.ctlStatus & "' And  [Level]='" & Me.ctlLevel & "'"
Else
strWhere = "[Level]='" & Me.ctlLevel & "'"
End If
End If
End If
DoCmd.OpenReport "Leadership Essentials by Employee", acViewPreview, , strWhere
Error_Handler:
DoCmd.CancelEvent
End Sub

Once again, thanks very much for your help.

 

[boblarson]

Glad you got it working.

 

[monkeysee]

Bob, I ran across this thread as I am in a similar project. I have a form with 1 combo box and 2 text boxes all are unbound. My combo box is named combo6 and and the text boxes are text2 and text4.
The combo box houses the EmployeeName; and the text boxes will house VisitDate. My report: EmpBalSheet. The form is: EmpBalSheetFilter. The Query is QryEmpBalSheet with the fields EmployeeName and VisitDate.
My Event procedure (DoCmd.OpenReport "EmpBalSheet", acViewPreview, , "[VisitDate] Between #" & Me.Text2 & "# And #" & Me.Text4 & "#") opens the report and displays the correct date range that has been chosen. My problem is I am stuck on how to add the EmployeeName combo box to the procedure. Hoping that you can shed some light on my dilemma. Thank you in advance!

 

[Micron]

@monkysee: introducing your own issue in someone else's thread is termed hijacking and is generally frowned upon. You should start your own, and if you think it would help, include a link to this thread. Not trying to be rude or anything. It's just that if I don't tell you, somebody else will at most, and at least, no one is likely to offer a solution.

koolwaters: if you're interested in another take:

Private Sub Combo17_DblClick(Cancel As Integer)
Dim lngCmb17 As Long: lngCmb17 = Len(Me.Combo17 & "")
Dim lngCmb30 As Long: lngCmb30 = Len(Me.Combo30 & "")
Dim strWhere As String

'assume Combo17 is Status and Combo30 is Level
Select Case True

Case lngCmb17 > 0 And lngCmb30 = 0
   'strWhere = "[Status]='" & Me.ctlStatus & "' "
   
Case lngCmb17 = 0 And lngCmb30 > 0
   'strWhere = "[Level]='" & Me.ctlLevel & "' "
   
Case lngCmb17 > 0 And lngCmb30 > 0
   'strWhere = "[Status]='" & Me.ctlStatus & "' And  [Level]='" & Me.ctlLevel & "'"

End Select

DoCmd.OpenReport "Leadership Essentials by Employee", acViewPreview, , strWhere

End Sub

I had 2 combos, 17 and 30 on a test form that I played with and got it to work using message boxes for when either control was Null, or both were, or neither. I learned a lot here about multiple criteria in a Select Case statement and the issues raised by trying to use operators such as <>. Seems impossible after a long attempt to get it working. Seems you don't need a comparison for when both are Null - opening report without passing anything to the Where parameter doesn't seem to cause an issue. I got all records in my testing when that was the case.