IMPROVED: SelectedListBoxItems() Function

Here's an improved version of my SelectedListBoxItems function based on feedback from Laurent Meus in the comments of that article.  For context and usage information, please refer to the above link.

Rather than looping through every item in the list box and then checking to see whether or not it's selected, Laurent uses the list box's ItemsSelected property:

Use the ItemsSelected property to return a read-only reference to the hidden ItemsSelected collection. This hidden collection can be used to access data in the selected rows of a multiselect list box control.

The ItemsSelected collection is unlike other collections in that it is a collection of Variants rather than of objects. Each Variant is an integer index referring to a selected row in a list box or combo box.

The (Improved) Code

The code below produces the exact same results as my original SelectedListBoxItems() function.  It's simply been refactored for improved readability.

'---------------------------------------------------------------------------------------
' Procedure : SelectedListBoxItems
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/selectedlistboxitems-v2/
' Date      : 12/14/2012 - 7/15/2022
' Purpose   : Returns a comma delimited list of selected items from a list box.
' Notes     - Wrapper generally "" for numeric, "#" for dates, and """" for strings.
'---------------------------------------------------------------------------------------
'
Function SelectedListBoxItems(LBox As ListBox, _
                              Optional Wrapper As String = "", _
                              Optional ColumnNum As Integer = 0)
    Dim s As String, CurItem As String, ItemIndex As Variant
    For Each ItemIndex In LBox.ItemsSelected
        CurItem = Wrapper & LBox.Column(ColumnNum, ItemIndex) & Wrapper
        s = Conc(s, CurItem)
    Next ItemIndex
    SelectedListBoxItems = s
End Function

'The Conc() function concatenates two strings without needing to strip
'   off the leading/trailing delimiter
'Source: https://nolongerset.com/come-together/
Private Function Conc(StartText As Variant, NextVal As Variant, _
                      Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function

Thanks for the suggestion, Laurent!

Extended Functionality

Over on LinkedIn, Access MVP Dale Fye mentioned that his version of this function incorporates logic to switch between In (...) and = ....  

In other words, instead of creating a WHERE clause like WHERE ID In (1), Dale's code would create a clause like WHERE ID = 1.  The two clauses are functionally identical; they will generate the same results.  Furthermore, I assume most databases will optimize to create identical query plans for both approaches.

What I find more interesting is Dale's additional incorporation of code to return either IS NULL or IS NOT NULL, depending on the circumstances.  

Here's Dale's comment:

I have a similar function but it actually provides not just the list, but how the criteria should be applied, so the call looks something like:

strSQL = "SELECT ... " _
& ("WHERE [SomeField] " + MultiSelect(me.lstname, 0, "") )

If no items are selected, the function returns NULL and use of the "+" in the above syntax eliminates the where clause. If there is a single item selected, the function returns "= 23" or whatever so that an IN ( ) clause is not used if not needed. Otherwise, it would return " IN (23, 24, 25)"

This particular version treats all items selected and no items selected the same and returns the NULL to basically remove the criteria for that particular field. Another version handles those differently and returns either " IS NULL" for no items selected or " IS NOT NULL" for all items selected.

Referenced articles