multi-select list box selections as criteria for a query

[jlrrush]

I need advice on taking multiple selections from a list box and applying those selections to the criteria for a query that controls report data.
I more or less want to filter data on a report from the selections in a list box. I'm not afraid to use code.

 

[RoyVidar]

In stead of passing it to a query, perhaps consider using the where condition of the openreport method of the docmd object?

thread702-787778 should give a workable solution.

Roy-Vidar

 

[willir]

jlrrush

Here is a recent post that reviews multi-select ListBoxes for contents. It can be tweaked to meet your needs.
(not tested, but something like...)

Private Sub ComittRecord_Click()

Dim lst As ListBox
Dim varItem As Variant
Dim strIN as String, strSQL as String, strQ as String

strQ = Chr$(34)

If lst.MultiSelect > 0 Then
    If lst2.ItemsSelected.Count > 0 Then
          For Each varItem2 In lst.ItemsSelected
                 strIn = strIn & " " & lst.ItemData(varItem)
          Next varItem2
    End If
End If

If Len(strIn) > 0 Then
    strSQL = "SELECT * from YourTable Where YourTestField IN " _
    & strQ & Trim(strIn) & strQ
    'You can now use this SQL statement as a record source or
    'YourFieldorSubForm.RecordSource = strSQL   type of thing
    'or for DAO or ADO or a QueryDef.
Else
    Msgbox "Please select something from the listbox first", vbOkOnly
End If

End Sub

I am assuming the listbox contains text strings.

Richard

 

[FancyPrairie]

Here's a way you can do it that will handle multiple multi-select list boxes, not just one. You can also use the same technique for date ranges, etc.

Note that the routine assumes that the Tag property of the listbox(es) is formatted as Where=TableName.FieldName,DataType,. Where DataType is either the word String or Number. That way the function knows how to format the SQL string.

Also note that the function uses the Bound column of the list box as the value to be included in the Where clause.

Assuming the function encounters no errors, you could call it by passing the form object. Something like this:

Docmd.OpenReport "rptName",intView,,BuildWhereClause(Me)

Function BuildWhereClause(frm As Form) As String

'********************************
'*  Declaration Specifications  *
'********************************

    Dim ctl As Control
    
    Dim varItem As Variant
    
    Dim strAnd As String
    Dim strField As String
    Dim strFilter As String
    Dim strType As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    On Error GoTo ErrHandler

'****************
'*  Initialize  *
'****************

    strFilter = vbNullString
    strAnd = vbNullString
    
'*********************************************************
'*  Loop thru all controls on form to find list box(es)  *
'*********************************************************

    For Each ctl In frm.Controls
                
        If (ctl.ControlType = acListBox) Then
                
        '*************************************************************************************************
        '*  Should this list box be processed?                                                           *
        '*  If so, then tag property contains the name of the table and field and the type of the field  *
        '*      (Structure of tag property:  Where=TableName.FieldName,DataType,    )                    *
        '*      NOTE that the code assumes the tag property is structured properly                       *
        '*************************************************************************************************
            
            If ((ctl.Enabled) And (Not ctl.Locked) And (ctl.ItemsSelected.Count > 0) And (InStr(ctl.Tag, "Where=") > 0)) Then
                
                j = InStr(ctl.Tag, "Where=")
                k = InStr(j, ctl.Tag, ",")
                strField = Mid(ctl.Tag, j + 6, k - (j + 6))
                
                j = InStr(k + 1, ctl.Tag, ",")
                strType = Mid(ctl.Tag, k + 1, j - k - 1)
                
                strFilter = strFilter & strAnd & strField & " In ("
                
        '******************************************
        '*  Loop thru items selected in list box  *
        '******************************************
        
                For Each varItem In ctl.ItemsSelected
                    
                    If (strType = "String") Then
                        strFilter = strFilter & "'" & ctl.Column(ctl.BoundColumn - 1, varItem) & "', "
                    ElseIf (strType = "Number") Then
                        strFilter = strFilter & ctl.Column(ctl.BoundColumn - 1, varItem) & ", "
                    End If
                    
                Next varItem
    
                strFilter = Mid(strFilter, 1, Len(strFilter) - 2) & ") "
                strAnd = " AND "
                
            End If
        End If
    
    Next
    
'***********************************
'*  Return Where clause to caller  *
'***********************************

    BuildWhereClause = strFilter
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    BuildWhereClause = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function

 

[willir]

No wonder you title is FancyPrairie!

 

[FancyPrairie]

I alluded to how you could use the same technique for date ranges. Somebody in this thread thread181-897708 ask, initially, basically the same question. So, if you're interested, check it out.