By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,070 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,070 IT Pros & Developers. It's quick & easy.

Multi-Select Listbox

P: n/a
How can I use the selections in a multi-select listbox as criteria in a query?


Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
Hash: SHA1

I use a VBA routine that gets the items (or item IDs) from the list box
and put those items in a comma-delimited string. Then use the string in
a WHERE IN expression. E.g.:

WHERE DeptID In (25, 30, 400, 6000)

The numbers in the parentheses are the DeptIDs that were selected from
the list box.

Here's the function:

Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' This code assumes the List Box's BoundColumn holds the IDs.
' In:
' lst A ref to a list box control
' intType One of the dbText, dbNumeric, etc. constants.
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
Dim row As Variant
Dim strIDs As String

For Each row In lst.ItemsSelected
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
Replace(lst.ItemData(row), "'", "''") & "',"
Case dbDate, dbTime
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row

' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)

End Function

Build the SQL string & then call this function to get the items selected
in the ListBox, like this:

dim strIDs as string
strIDs = getIDs(Me!lstDepartments, dbNumeric)

' Then add the IDs to the SQL string:
strSQL = strSQL & " AND DeptID IN (" & strIDs & ") "

Then carry on w/ the SQL string - run it or put it into a query def,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Version: PGP for Personal Privacy 5.0
Charset: noconv

Mark wrote:
How can I use the selections in a multi-select listbox as criteria in a query?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.