Filter Report by Multi Select Listbox (1 Viewer)

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?

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 & ")"

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:

The report filter code:

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


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(*)"

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

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

And that's exactly what I did

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

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.

Happy to help!

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,

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.

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.

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

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

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.

Happy to help!

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!