Filter a report based on a couple of multiselect listboxes

[bakkouz]

Hey guys,
I have a report that is filtered based on a multiselect listbox on a form.
which is working fine.
what i want to do is add another multiselect listbox and filter the report based on both listboxes.
which i don't really know how to do.

Here is the code i'm using:

Private Sub Command7_Click()
Dim strReport As String
Dim strWhere As String
Dim lngView As Long
Dim strFilter As String
Dim varItem As Variant

strReport = "Main_DB_Report" 'Put your report name in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

' loop through listbox items selected
For Each varItem In Me!List0.ItemsSelected
strFilter = strFilter & "[country] = '" & _
Me!
[List0].ItemData(varItem) & "' OR "


Next ' continue loop


If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
Else
MsgBox "You did not select any Country."
List0.SetFocus
Exit Sub
End If


DoCmd.OpenReport strReport, acViewPreview, , strFilter
End Sub

I've attached the sample database.productiondb.zip

[June7]

What is the second listbox for?

Maybe have another loop for the second listbox then concatenate the two strings with an AND operator.

Make sure each string of OR criteria will be enclosed in parenthesis, like:

(lb1a OR lb1b OR lb1c) AND (lb2a OR lb2b OR lb2c)

[bakkouz]

June,
The other listbox is for year.
and i'm not really sure how to implement your instructions, i'm a beginner still, sorry.
do i just replicate the above code for the loops and adjust it to the second list box?
and where would i concatenate the two strings with an AND operator?

[June7]

Suggest two more variables:

strFilter1
strFilter2

Build each string with looping structures.

Concatenate:

strFilter = "(" & Left(strFilter1, Len(strFilter1) - 4) & ") AND (" & Left(strFilter2, Len(strFilter2) - 4) & ")"

[bakkouz]

June,
I have done as you instructed.
but now when clicking the button i am getting:
error 2205 (default printer driver is not set up correctly)

Here is the full code:
Private Sub Command7_Click()
Dim strReport As String
Dim strWhere As String
Dim lngView As Long
Dim strFilter As String
Dim strFilter3 As String
Dim strFilter2 As String
Dim varItem As Variant

strReport = "Main_DB_Report" 'Put your report name in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

' loop through listbox items selected
For Each varItem In Me!List0.ItemsSelected
strFilter = strFilter & "[country] = '" & _
Me!
[List0].ItemData(varItem) & "' OR "
Next ' continue loop
'
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
Else
MsgBox "You did not select any Country."
List0.SetFocus
Exit Sub
End If

' loop through listbox items selected
For Each varItem In Me!List5.ItemsSelected
strFilter2 = strFilter2 & "[year] = '" & _
Me!
[List5].ItemData(varItem) & "' OR "
Next ' continue loop
'
' the next bit of code will subtract out the last "OR"
If strFilter2 <> "" Then
strFilter2 = Left(strFilter2, Len(strFilter2) - 4)
Else
MsgBox "You did not select any year."
List5.SetFocus
Exit Sub
End If

'
' now, run the report using strFilter to pass a string
' containing the needed customers
strFilter3 = "(" & Left(strFilter, Len(strFilter) - 4) & ") AND (" & Left(strFilter2, Len(strFilter2) - 4) & ")"


DoCmd.OpenReport strReport, acViewPreview, , strFilter3


End Sub

[June7]

Don't know why that error would trigger.

Have you step debugged? Follow the code as it executes. Does filter string get properly constructed? Refer to link at bottom of my post for debugging techniques.

You probably want to use conditional code in the concatenation of the two filter strings, in case one or both are empty so don't end up with something like:

( ) AND (value1 OR value2)