Filtering Based on a Multi-Select Listbox

[briandrago]

How can I create a code or macro that will allow me to select several options for a listbox and then filter a report based on these selections.

I can do it for only one selection but it can't find a match with what I'm looking for when I select multiple.

I've visited the baldy help website but it's not working for me.

 

[pbaldy]

How is it not working? There would need to be tweaks if the value was text instead of numeric.

 

[pbaldy]

Actually, I just realized I gave you a different page on your other thread, so wanted to make sure you saw this:

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

 

[briandrago]

File when zipped is still too large to post, sorry

 

[briandrago]

I think the code works fine except for the last part, I keep getting an error that says that there is a syntax error.

Why do you have EmpID in the last part of the code, should I enter the primary key from the query that I'm getting the information?

 

[pbaldy]

Yes, EmpID is the field to be filtered on in my example data. You would have to substitute yours. If you're still stuck, post your code and the data type of the field.

 

[briandrago]

I basically copy and pasted the code from your help website and substituted my report and field names

Here's the code, and its supposed to be looking up text in a query and outputting a report:

Private Sub Command29_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.List25.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.List25
For Each varItem In ctl.ItemsSelected
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 "DRP Forecast Accuracy", acPreview, , "Brand IN(" & strWhere & ")"
End Sub

Here is the message that I receive when I run it, for example when I choose Bagels and Beyond fries from my listbox this is what I see:

Run-time error '3075':

Syntax error (missing operator) in query expression '(Brand IN(Bagels, Beyond Fries))'.

 

[pbaldy]

Try

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

 

[briandrago]

So you're the man, what was the difference between the two sets of code?

It worked by the way.

 

[pbaldy]

Because your values are text rather than numeric, they have to be enclosed in single quotes. My last post added those:

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

 

[briandrago]

Now I see why you're microsoft MVP 2007, thanks a lot.

You can mark this thread as solved.

 

[ChronicFear]

If I may hijack this thread...

pbaldy, how might I extend your logic to filter by multiple catergories? So if I have two mulitselect listboxes on my form, how can I get the code to loop through and filter by both?

Thanks,
CF

 

[pbaldy]

If I'm understanding correctly try:

For Each varItem1 In ctl1.ItemsSelected
  For Each varItem2 In ctl2.ItemsSelected
    'do your thing
  Next varItem2
Next varItem1

 

[ChronicFear]

Ok, that is helpful for the first part, thanks. I don't fully understand how Access thinks about filtering reports, so I'm also not sure how to write the filter for the open event.

Perhaps I should back up:

I have a form that allows users to customize a report. It contains many multiselect list boxes, e.g.

Status
Green
Yellow
Red

Quarter
Q1
Q2
Q3
Q4

and on and on. What I would like to do is be able to filter the report so that if a user wants to see everything with a status of red or green in the first quarter, they can just select those items in the list box and then the report will filter.

There are 6 listboxes currently. If you know of an easier/better way to handle this type of data, I would sincerely appreciate suggestions.

Thanks for your help,
CF

 

[pbaldy]

Actually I misunderstood. Will they have to make at least one selection from each? Presuming so, you'd repeat the loop, not nest it, building a different string for each. Then this type of thing:

DoCmd.openreport "DRP Forecast Accuracy", acPreview, , "Brand IN(" & strWhere & ") AND Whatever IN(" & AnotherVariable & ")..."

 

[ChronicFear]

Brilliant!

Thanks, Paul.

 

[ChronicFear]

If I may indulge myself, perhaps you can help me with another related problem.

So instead of having the user select every option in the listbox or having the listbox default to highlighting everything, I used some code from this site to add a dummy (All) choice at the top of my lists. Unforuntately, the kind gentleman who provided the code did not provide any code for handling the variable.

I assume it's just some sort of If statement, but once I've identified that the All variable has been selected, how do I tell Access to act as if every item in my listbox had been individually selected?

Thanks,
CF

 

[pbaldy]

Like everything, there are different ways of doing this. The one I would choose would depend on the specifics. If the choices were very static, I might simply add them all and leave the field in the wherecondition:

If Whatever Then
  Variable = "'Q1','Q2','Q3','Q4'"
Else
  Your loop here
End If

My tendency is actually to leave it out completely, so more like:

If TheyDidn'tMakeAChoiceOrChoseALL Then
  'don't do anything
Else
  'Add that field to the wherecondition with the choice(s) they made
End If

 

[pbaldy]

Just happened to stumble on an actual usage while working on something else. In this instance, code previous has built an SQL string. I have a checkbox next to the listbox the users can check if they want all:

  If Not Me.chkAllRoutes Then
    Set ctl = Me.lstRoutes

    For Each varItem In ctl.ItemsSelected
      strIN = strIN & "'" & ctl.ItemData(varItem) & "', "
    Next varItem

    strIN = Left(strIN, Len(strIN) - 2)

    strSQL = strSQL & " AND RouteID IN(" & strIN & ")"
  End If

So the field along with its criteria are only added to the string if the user didn't check the box. If they check the box, nothing is added to the SQL, which means all records will be returned.

 

[ChronicFear]

Paul,

Thanks again for your help. As usual, I often get hung up trying to figure out how to make Access do something, when the easiest answer is just to make Access do nothing.

I also like your check box idea, but I think that I'll get push-back from users (you know how people get) that there are too many options and they are confused, so I think I'll try to keep the All option within my lists and just assign it a value that I can reference.

Cheers,
CF