Using Multiselect to filter report criteria.

[gilnutz]

Hello,

I apologize ahead of time if this question has already been answered. I didn't find what I needed while searching the forum. I am a beginner with Access and am using a form to filter criteria for a report. I am using a list box to select what type of service calls to report on. When I use the list box without multiselect and select one item from the list the report works. When I use multiselect the report is blank. I have the list box ROW SOURCE VALUE set as a value list and the ROW SOURCE is enter like the following "Artesian Well";"Dirty Water";"Health Concerns";"Hot Water Tanks";"Plumbing Problems";"Pressure Questions";"Taste & Odor";"Water Quality Info Request" I've tried using table/query for the ROW SOURCE VALUE but when I do that the list shows all duplicates. Any help will be greatly appreciated!

Billy

 

[pbaldy]

http://allenbrowne.com/ser-50.html

 

[gilnutz]

Thank You

Thanks for your help, I couldn't get that to work with my Access but maybe there is somthing else going on. I'm running Access 2000. I get an error when selecting more then one product in North Wind. It says "Error 3075 - Syntax error (missing operator) in query expression '(,)'. Any idea what that could be?

Billy

 

[pbaldy]

I also have 2000, and just followed the directions and it worked perfectly. Double check that you followed his directions and copied/pasted the code. If it still doesn't work, post the code here.

 

[gilnutz]

Here is a copy of the code.

Here is a copy of the code I entered also here is another error message. The first error message I posted was if ONE item is selected this message comes up if there are more then one items selected.

Error Message "Error 3075 - Syntax error (missing operator) in query expression '([CategoryID]IN (,))'."


Code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub lstCategory_BeforeUpdate(Cancel As Integer)

End Sub

 

[pbaldy]

Tell you what; here's my working one:

 

[gilnutz]

Thanks Paul, Your copy of Northwind works fine. I will work on this today.
I found out what I was doing wrong. In the list box columns on the new form I set the Bound Column to 0, once I set that to 1 the report generated just fine. Thanks again for your help.

Billy

 

[Dre1tt]

This example works great with numbers, but how would you make it work with text values?

 

[Dre1tt]

This example works great with numbers, but how would you make it work with text values?

Never mind, got it!