Hi
I have a report that I want to filter based on user input.
I found this FAQ from Randy Smith ( which got me on my way and works well with only 1 list box, but it's now getting complicated because I need several list boxes.
Here's the code so far, with only 2 variables:
[tt]
Private Sub btnPrintReport_Click()
Dim strFilter As String
Dim varItem As Variant
' loop through Area/Function listbox items selected
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & "[Area/Function] = " & _
Me![lstAreaFunction].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
If IsNull(strFilter) Then
strFilter = ""
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & "[Category] = " & _
Me![lstCategory].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
Else
'Add "AND" to strFilter
strFilter = strFilter & " And "
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & "[Category] = " & _
Me![lstCategory].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
End If
DoCmd.OpenReport "Area/Function", acPreview, , strFilter
End Sub[/tt]
The problem with this is that I have a number of filters that are optional. At this rate, I'll have nested If statements up the wazoo and the coding will get very ugly.
Does anyone know of an easy way to have several multi-select list boxes on a form and to pass those values to a report filter? Thanks in advance.
Jim DeGeorge![[wavey]](/data/assets/smilies/wavey.gif "[wavey] [wavey]")
I have a report that I want to filter based on user input.
I found this FAQ from Randy Smith ( which got me on my way and works well with only 1 list box, but it's now getting complicated because I need several list boxes.
Here's the code so far, with only 2 variables:
[tt]
Private Sub btnPrintReport_Click()
Dim strFilter As String
Dim varItem As Variant
' loop through Area/Function listbox items selected
For Each varItem In Me!lstAreaFunction.ItemsSelected
strFilter = strFilter & "[Area/Function] = " & _
Me![lstAreaFunction].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
If IsNull(strFilter) Then
strFilter = ""
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & "[Category] = " & _
Me![lstCategory].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
Else
'Add "AND" to strFilter
strFilter = strFilter & " And "
' loop through Category listbox items selected
For Each varItem In Me!lstCategory.ItemsSelected
strFilter = strFilter & "[Category] = " & _
Me![lstCategory].ItemData(varItem) & " OR "
Next ' continue loop
' the next bit of code will subtract out the last "OR"
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
End If
DoCmd.OpenReport "Area/Function", acPreview, , strFilter
End Sub[/tt]
The problem with this is that I have a number of filters that are optional. At this rate, I'll have nested If statements up the wazoo and the coding will get very ugly.
Does anyone know of an easy way to have several multi-select list boxes on a form and to pass those values to a report filter? Thanks in advance.
Jim DeGeorge
