Multiple MultiSelect List Boxes for Where Clause

[CantDo]

I have a form for filtering data on a report. The form has several list boxes with multiselect set to extended. I want to pass any or all criteria selected on the form to a report so as to filter results based on the user's desires. If it were only one criteria, the solution would be simple -- use a 'For Each / Next' section to collect the items selected, then create a '[ReportField] In {Collection}' statement for the Where clause of the OpenReport command.

Can anyone help with multiple criteria? Is it possible to nest or string IN parameters for use with the Where clause? I would post code, but I'm sorta stuck with how to begin.

Any help would be most appreciated. Thanks.

 

[Remou]

How about:

For Each varItem In Me.List0.ItemsSelected
   lst = lst & "," & Chr(34) & Me.List0.Column(1, varItem) & Chr(34)
Next varItem
strWhere = "Where ThisField In (" & Mid(lst, 2) & ")"

 

[CantDo]

Your code above appears to be able to work for one list, but in my situation, I have multiple lists (and a date control). So far, I have created the following code which works fine, albeit inelegantly.

If anyone knows of a more elegant solution, I'm all ears / eyes.

Private Sub GenerateReport_Click()

On Error GoTo Err_Btn_GenerateReport_Click
    
    Dim ctlOwner, ctlCategory, ctlStatus, ctlMatter, ctlBU, ctlContact As Control
    Dim varItem As Variant
    Dim stRptName, stLinkCriteria As String
    Dim stOwner, stCategory, stStatus, stMatter, stBU, stContact, stCalendarDate As String
    
    Set ctlOwner = Me!OwnerList
    Set ctlCategory = Me!CategoryList
    Set ctlStatus = Me!StatusList
    Set ctlMatter = Me!MatterList
    Set ctlBU = Me!BUList
    Set ctlContact = Me!ContactList
    stRptName = "rptInventory"
    
    For Each varItem In ctlOwner.ItemsSelected
        stOwner = stOwner & ctlOwner.Column(1, varItem) & """, """
    Next varItem
    If stOwner <> "" Then
        stOwner = "[Owner] in (""" & stOwner
        stOwner = Left$(stOwner, Len(stOwner) - 4)
        stOwner = stOwner & """)"
    End If
    
    For Each varItem In ctlCategory.ItemsSelected
        stCategory = stCategory & ctlCategory.Column(2, varItem) & """, """
    Next varItem
    If stCategory <> "" Then
        If stOwner = "" Then
            stCategory = "[FullCategoryDescription] in (""" & stCategory
        Else
            stCategory = " And [FullCategoryDescription] in (""" & stCategory
        End If
        stCategory = Left$(stCategory, Len(stCategory) - 4)
        stCategory = stCategory & """)"
    End If

    For Each varItem In ctlStatus.ItemsSelected
        stStatus = stStatus & ctlStatus.Column(2, varItem) & """, """
    Next varItem
    If stStatus <> "" Then
        If stOwner = "" And stCategory = "" Then
            stStatus = "[StatusColor] in (""" & stStatus
        Else
            stStatus = " And [StatusColor] in (""" & stStatus
        End If
        stStatus = Left$(stStatus, Len(stStatus) - 4)
        stStatus = stStatus & """)"
    End If
    
    For Each varItem In ctlMatter.ItemsSelected
        stMatter = stMatter & ctlMatter.Column(1, varItem) & """, """
    Next varItem
    If stMatter <> "" Then
        If stOwner = "" And stCategory = "" And stStatus = "" Then
            stMatter = "[MatterName] in (""" & stMatter
        Else
            stMatter = " And [MatterName] in (""" & stMatter
        End If
        stMatter = Left$(stMatter, Len(stMatter) - 4)
        stMatter = stMatter & """)"
    End If

    For Each varItem In ctlBU.ItemsSelected
        stBU = stBU & ctlBU.Column(1, varItem) & """, """
    Next varItem
    If stBU <> "" Then
        If stOwner = "" And stCategory = "" And stStatus = "" And stMatter = "" Then
            stBU = "[BusinessUnit] in (""" & stBU
        Else
            stBU = " And [BusinessUnit] in (""" & stBU
        End If
        stBU = Left$(stBU, Len(stBU) - 4)
        stBU = stBU & """)"
    End If

    For Each varItem In ctlContact.ItemsSelected
        stContact = stContact & ctlContact.Column(1, varItem) & """, """
    Next varItem
    If stContact <> "" Then
        If stOwner = "" And stCategory = "" And stStatus = "" And stMatter = "" And stBU = "" Then
            stContact = "[Contact] in (""" & stContact
        Else
            stContact = " And [Contact] in (""" & stContact
        End If
        stContact = Left$(stContact, Len(stContact) - 4)
        stContact = stContact & """)"
    End If
    
    If DueDateCalendar.Value <> "" Then
        If stOwner = "" And stCategory = "" And stStatus = "" And stMatter = "" And stBU = "" _
         And stContact = "" Then
            stCalendarDate = "[DueDate] <= Nz(Forms!frmReportFilter!DueDateCalendar.Value)"
        Else
            stCalendarDate = " And [DueDate] <= Nz(Forms!frmReportFilter!DueDateCalendar.Value)"
        End If
    Else
        stCalendarDate = ""
    End If

stLinkCriteria = stOwner & stCategory & stStatus & stMatter & stBU & stContact & stCalendarDate
    
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria
   
Exit_Btn_GenerateReport_Click:
    Exit Sub
    
Err_Btn_GenerateReport_Click:
    MsgBox Err.Description
    Resume Exit_Btn_GenerateReport_Click

End Sub

 

[Remou]

Have a look at:
Build Report Criteria via a Form w/list box, text box, date range
faq181-5497
You may find it interesting.

 

[CantDo]

Thanks, Remou. I had seen that FAQ before, but it doesn't seem to help me. I think this is because it requires a tag for each list, etc. In my case, the lists are unbound and obtained through a form query selecting several fields across several tables that have relationships to one another.

In any event, I tried using the FAQ, but got stuck with the tag input since it did not recognize the table/field structure required.