multi-select list box to filter a report (1 Viewer)

I am trying to get this to work but I cannot figure it out.
At this point I'm getting an object required error on the docmd.openreport line. I'm sure its because I don't have the where clause set up properly. If I debug strwhere it has the data for the where clause. The table has an id field and a category field. I want to limit based on the category. The report is based on a query of all records in the vol_request_table. Here is the SQL for the query behind the report

SELECT Members_Table.Member_Id AS Members_Table_Member_Id, Members_Table.First_Name, Members_Table.Last_Name, Volunteer_Categories_Table.Volunteer_Category_Id AS Volunteer_Categories_Table_Volunteer_Category_Id, Volunteer_Categories_Table.Volunteer_Category, Volunteer_Request_Table.Member_Id AS Volunteer_Request_Table_Member_Id, Volunteer_Request_Table.Volunteer_Category_Id AS Volunteer_Request_Table_Volunteer_Category_Id, Volunteer_Request_Table.Comments
FROM Volunteer_Categories_Table INNER JOIN (Members_Table INNER JOIN Volunteer_Request_Table ON Members_Table.[Member_Id] = Volunteer_Request_Table.[Member_Id]) ON Volunteer_Categories_Table.[Volunteer_Category_Id] = Volunteer_Request_Table.[Volunteer_Category_Id];

This is the event procedure.
Private Sub RunReport_Click()

On Error GoTo Err_RunReport_Click

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

'make sure a selection has been made
If Me.VolCatSel.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 category"
Exit Sub
End If

'add selected values to string
Set ctl = Me.VolCatSel
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 "Volunteer_Requests_Report", acPreview, , ((Volunteer_Categories_Table.Volunteer_Category) = (" & strWhere & "))


Exit_RunReport_Click:
Exit Sub

Err_RunReport_Click:
MsgBox Err.Description
Resume Exit_RunReport_Click
End Sub

I have an example here:

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

Note how the wherecondition is a string (enclosed in quotes).

I changed it to this
DoCmd.OpenReport "Volunteer_Requests_Report", acPreview, , "Volunteer_Categories_Table.Volunteer_Category IN(" & strWhere & ")"

And got the following error
syntax error(missing operator) in query expression "volunteer_Categories_Table.Volunteer_Category IN(general help)"

General help is the category selected from the list box. I haven't even tried multiple selections yet. Just trying to get one to work

Check my link again, and note the different syntax required for text data types (the commented out lines).

Thank you so much. It took me a few minutes to see what you were referring to. Once I made the switch it worked great.

Excellent, glad it worked for you. Welcome to the site by the way.

Hi pbaldy

I have tried to run your code and am still getting the same error.
Syntax error (missing operator) in query expression 'Application Name('FAS')'

Do you know why this could be? I am a total newbie and could really use your help!

The data I am trying to filter by is text. Here is my code:

Private Sub button0_Click()

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

'make sure a selection has been made
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Application"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List0
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 "Report1", acViewReport, , "Application Name(" & strWhere & ")"


End Sub

You missed the word "IN", and the inadvisable space will require the field name to be bracketed.

Oh my goodness, thank you so much! I have been working on this for longer than I want to admit. I must have pasted the version without the 'IN' (I have been switching up random words to try and make it work) but it was the brackets all along. As I said, total total newbie. I can not thank you enough!

I have 2 more questions if you are up for it-

1) Is there a way to add an "All" all option to this list so that the user can get a report with all of the items without having to click on one individually?

2) Would it be easy to build upon this, giving the user the option to filter by two categories (I.e. Application Name, Job Site)?


Thank you again! You saved the day!

Happy to help!

1) If appropriate to your situation, you can switch the test for nothing checked to open the report unfiltered. Otherwise, you can put a check box on the form and test that.

2) Shouldn't be too difficult. Number 1 above will complicate it a bit, but basically you just have to think through the logic of what you want and then code for it. This tutorial and the sample db may be of interest:

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

You really are a lifesaver! And your website has such great content!

I am putting the 'Select all' aside for now and working on the double filter. I may have done this in a sort of cheap way, but i basically just copied all of the code and added a '2' to all of the variables. But it is getting be back to that error about no expressions.

I am thinking it is the last statement that is the issue since I am not really sure how to combine those. And to clarify, I am hoping to use this filter down more (I would like the 2 filter statements to have an and relationship, not an or. So showing only the results having that application AND job site.)

Here is the code I have so far:

Private Sub button0_Click()

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

'make sure a selection has been made
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Application"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List0
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

Dim strWhere2 As String
Dim ctl2 As Control
Dim varItem2 As Variant

If Me.List3.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Application"
Exit Sub
End If

'add selected values to string
Set ctl2 = Me.List3
For Each varItem2 In ctl2.ItemsSelected
strWhere2 = strWhere2 & "'" & ctl2.ItemData(varItem2) & "',"
Next varItem2
'trim trailing comma
strWhere2 = Left(strWhere2, Len(strWhere2) - 1)


'open the report, restricted to the selected items
DoCmd.OpenReport "Report1", acViewReport, , "[Application Name] IN (" & strWhere & ")" & "[Site Name] IN (" & strWhere2 & ")"


End Sub

Haven't looked at the rest, but the multiple conditions would be separated by "AND":

DoCmd.OpenReport "Report1", acViewReport, , "[Application Name] IN(" & strWhere & ") AND [Site Name] IN(" & strWhere2 & ")"

I really can not thank you enough! It is working perfectly!!!

I am still trying to get the functionality working for the "All" function and haven't gotten much luck. I tried to add "All" to the listbox and say in then set the strWhere = * but it did not work. I am pretty sure I am way off, but I was wondering if you had any insight at all about this issue.

I am so grateful for all of your help!!! Thankfully, this is the one roadblock that stands in the way of me completing project!


Here is a sample of what I tried out:

If Me.List4.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Application"
Exit Sub
End If
If Me.List4.ItemsSelected.Item = "All" Then
strWhere3 = "*"
Else

'add selected values to string
Set ctl3 = Me.List4

For Each varItem3 In ctl3.ItemsSelected
strWhere3 = strWhere3 & "'" & ctl3.ItemData(varItem3) & "',"
Next varItem3
'trim trailing comma
strWhere3 = Left(strWhere3, Len(strWhere3) - 1)
End If

Rather than thinking about it as having to include all items for that field, you can simply exclude that field from the criteria.

Good thought! Any guidance on how I would do that?

If you still have 2, you leave the string blank:

If Me.List4.ItemsSelected.Item = "All" Then
strWhere3 = ""
Else
...

then at the end test for them being blank: