Building WHERE ... IN (list) from multiselect listbox

Question

What is the best (and easiest for the user) way of setting multiple criteria for a search facility?
Ideally, I want the user to be able to choose options from several listboxes - I have done this OK for a single listbox, limited to single selections, but I can't get it to work when the listboxes are multiselect (simple).

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Search - Help desperately needed.
Date: 7 October 1998 4:28

Andy,

Try to use the return value from the enclosed function in IN clauses of the SQL-expression you build on-the-fly...

HTH,
Shamil

P.S. The code follows:

Function SelItemsListBuild(ByRef rfrm As Form, _
                          ByRef rlst As ListBox, _
                          Optional ByVal vintColumn As Integer = -1, _
                          Optional ByVal vblnText As Boolean = False) As String
'*-
'
' Shamil Salakhetdinov
' e-mail: shamil@marta.darts.spb.ru
'
' Arguments:
'
' rfrm - form object ref
' rlst - listbox control object ref
' vintColumn = -1 - means - use bound column through ItemData
' vblnText - set it to True for text values extracted from selected rows of listbox
'
' Note: BoundColumn property starts from 1
'       .Column column starts form Zero as well as Listbox's row index
'*-

    Dim strList2Return As String
    Dim var As Variant
    Dim strDQ As String
    
    If vblnText Then
        strDQ = """"
    End If
    For Each var In rlst.ItemsSelected
        If Len(strList2Return) <> 0 Then
            strList2Return = strList2Return & ","
        End If
        If vintColumn = -1 Then
            strList2Return = strList2Return & strDQ & rlst.ItemData(var) & strDQ
        Else
           strList2Return = strList2Return & strDQ & rlst.Column(vintColumn, var) & strDQ
        End If
    Next
    SelItemsListBuild = strList2Return
End Function