Filter Report by Multi Select Listbox

[acarterczyz]

Hey all,

I'm using pbauldy's code to filter a report by a multi-select listbox. The only issue is..how do I include more (7 to be exact) listboxes to the code?
It seems the OpenReport vba is only allowing 1 where clause?

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant


'add selected values to string
Set ctl = Me.VP_ListBox
For Each varItem In ctl.ItemsSelected
  'strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)



'open the report, restricted to the selected items
DoCmd.OpenReport "TestReport", acPreview, , "VP IN(" & strWhere & ")"

 

[pbaldy]

To use multiple listboxes, generate a different "strWhere" for each (like strWhereVP, strWhereOther, etc). Then

DoCmd.OpenReport "TestReport", acPreview, , "VP IN(" & strWhereVP & ") And Other IN(" & strWhereOther & ")"

 

[acarterczyz]

Works perfect! Thanks!

One more thing - what if the user omits selecting an option in from the listbox? I have it handling where it skips it where I define the strWhere string, but when it goes to filter the report, it will error out:

Example of my strWhere definition:

Set ctl = Me.Office_ListBox
If Me.Office_ListBox <> "" Then
For Each varItem In ctl.ItemsSelected
  strWhereOffice = strWhereOffice & "'" & ctl.ItemData(varItem) & "',"
Next varItem
strWhereOffice = Left(strWhereOffice, Len(strWhereOffice) - 1)
Else
End If

The report filter code:

DoCmd.OpenReport "TestReport", acPreview, , "VP IN(" & strWhereVP & ") And RVP IN(" & strWhereRVP & ") And RD IN(" & strWhereRD & ") And AM IN(" & strWhereAM & ") And AAM IN(" & strWhereAAM & ") And [Rep BDR Territory] IN(" & strWhereOffice & ") And RepName IN(" & strWhereRep & ")"

 

[acarterczyz]

And just to test out to see if this mattered, I set Else to:

Else
strWhereOffice = "*"
End If

And this still gave me the Run-time error:
"Syntax error (missing operator) in query expression 'VP IN() And RVP IN(*) And RD IN(*) And AAM IN(*) And [Rep BDR Territory] IN(*) And RepName IN(*)"

 

[acarterczyz]

This is fixed. I ended up just sending the results to a query and exporting the query to Excel.

 

[pbaldy]

The fix would have been to test each variable, and only add it to the final string if it got populated, like:

If Len(strWhereVP) > 0 Then
  strFinal = strFinal & " AND VP IN(" & strWhereVP & ")"
End If

 

[acarterczyz]

And that's exactly what I did

After I added in all the variables to the strWhereQuery, I did this:

If Me.PrintOptionFrame = 1 Then
CurrentDb.QueryDefs("QueryResults").SQL = "select * from SummaryQuery where " & strWhereQuery
DoCmd.RunMacro "SaveReportMacro"

Instead of filtering a report, I just sent the results to a query to filter and outputting the query results to Excel via macro.

Again, thank you soooo much Baldy! You're code has literally saved my day on many occasions.

 

[pbaldy]

Happy to help!

 

[mnaeem2k]

Hello,
I have a simple list box with multiple values and I have to open a report based on those values. I have followed all the steps. But while opening the report the parameter box is being displayed against each item of the list that has been selected with a blank prompt.

the list string is being built properly enclosed within ( ). The report is being called by:

DoCmd.OpenReport "Rep_MergedData", acPreview, , "origincity IN" & strCriteriaOC

Can anyone suggest why the prompt is still being displayed, blank and how to rectify this.


Thanks,

 

[pbaldy]

Use this to see what the finished string looks like:

http://www.baldyweb.com/ImmediateWindow.htm

Post it here if you don't spot the problem.

Edit: your image didn't come through.

 

[mnaeem2k]

Hi PBaldy,
Thanks for your quick reply.

I had been specifically following your post from 1 other location
I believe my sql string is being created correctly.

due to 10 posts restriction, i can't share any link or attachment.
i can email you separately if thats ok with you.

Button code
--------------------------------------
Private Sub Command2_Click()
Dim strCriteriaOC As String
Dim varItem As Variant

If Me.List1.ItemsSelected.Count = 0 Then
MsgBox "No Origin City was selected"
Exit Sub
End If

For Each varItem In Me.List1.ItemsSelected
strCriteriaOC = strCriteriaOC & Me.List1.ItemData(varItem) & ","
Next
If Right(strCriteriaOC, 1) = "," Then strCriteriaOC = Left(strCriteriaOC, Len(strCriteriaOC) - 1)
strCriteriaOC = "[origincity] IN (" & strCriteriaOC & ")"
MsgBox strCriteriaOC
DoCmd.OpenReport "Rep_MergedData", acPreview, , strCriteriaOC

End Sub.

 

[pbaldy]

The string is not being created correctly, it needs delimiters since the values are text. See here:

http://www.baldyweb.com/multiselect.htm

 

[mnaeem2k]

how stupid of me. completely forgot its a string.
Thank you very much Paul for your help.

Hope you don't mind if I keep troubling you again and again.


Regards,


Naeem.

 

[pbaldy]

Happy to help!

 

[ralsam]

Hello,

I am an active Access user, but am not great with the code side of things. I was able to set up a multi-select function to use information from a listbox to pass through a parameter to a query to feed a report. I am now trying to figure out how to add 4 other list boxes where you would select something in each list box (or not select so as to show everything). My original code is below. How do i modify it to allow someone to select a company from a listbox, select gender from a listbox and select a region from a listbox? These are all fields within the "Main Data" query. I have set up individual tables for each of these selections. "Summary" is a report that is fed by the Main Data query.

Option Compare Database
Option Explicit

Private Sub Command36_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("[Main Data]")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me![List112].ItemsSelected
strCriteria = strCriteria & ",'" & Me![List112].ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM [Main Data - All] " & _
"WHERE [Master Data]![Category] IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Summary", acViewPreview
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Thank you!