Filter a report based on a couple of multiselect listboxes

[bakkouz]

Hey guys, Beginner here..

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

 

[kayjenx]

Or you can filter your query that links to your report by the listboxes... in the criteria just add Forms!FormName!ListBoxName in the criteria.

Your code would just be an if statement if listbox blank, msgbox to say please choose, else, open report.

 

[spikepl]

@kayjenx

Or you can filter your query that links to your report by the listboxes... in the criteria just add Forms!FormName!ListBoxName in the criteria.

Not for a multiselect listbox.

 

[KiEESH]

Hey all!

Trying to run a query based on either one multiselect listbox OR another.

Trouble is...I'm not sure how to tell access to run based on one OR the other.

'lst_AdHocPhase' works perfectly.

However, 'lst_Zone' is completely ignored when I make a choice (ie. the query returns zero records)...even when the Phase list has not been selected.

Here's my code. Any suggestions would be greatly appreciated!


On Error GoTo Err_btn_RunAdHocQry_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
'-----------------------------------------------------------------------------
Set MyDB = CurrentDb()
'------------------------------------------------------------------------------
strSQL = "SELECT * FROM AREA_GROWTH2"
'------------------------------------------------------------------------------
'Build the IN string by looping through the listbox
For i = 0 To lst_AdHocPhase.ListCount - 1
If lst_AdHocPhase.Selected(i) Then
If lst_AdHocPhase.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lst_AdHocPhase.Column(0, i) & "',"
End If
Next i

For i = 0 To lst_Zone.ListCount - 1
If lst_Zone.Selected(i) Then
If lst_Zone.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lst_AdHocPhase.Column(0, i) & "',"
End If
Next i
'------------------------------------------------------------------------------
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Phase] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'------------------------------------------------------------------------------
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
'------------------------------------------------------------------------------
MyDB.QueryDefs.Delete "qry_AdHoc"
Set qdef = MyDB.CreateQueryDef("qry_AdHoc", strSQL)
'------------------------------------------------------------------------------
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qry_AdHoc", acViewNormal
DoCmd.Close
strfilename = "C:\Documents and Settings\" & Environ("username") & "\Desktop\AGDB_AdHocQuery.xls "
DoCmd.TransferSpreadsheet acExport, , "qry_AdHoc", strfilename
MsgBox "Successful Export to Desktop", , "File Export"

'------------------------------------------------------------------------------

'Clear listbox selection after running query
For Each varItem In Me.lst_AdHocPhase.ItemsSelected
Me.lst_AdHocPhase.Selected(varItem) = False
Next varItem

'------------------------------------------------------------------------------

Exit_btn_RunAdHocQry_Click:
Exit Sub

Err_btn_RunAdHocQry_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection from the list" _
, , "Selection Required !"
Resume Exit_btn_RunAdHocQry_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_btn_RunAdHocQry_Click
End If

End Sub