How can I use the selections in a multi-select listbox as criteria in a query?
Thanks!
Mark
Thanks!
Mark
-
-
Re: Multi-Select Listbox
-----BEGIN PGP SIGNED MESSAGE-----
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.ItemsSelect ed
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
Replace(lst.Ite mData(row), "'", "''") & "',"
Case dbDate, dbTime
strIDs = strIDs & "#" & lst.ItemData(ro w) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(ro w) & ","
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!lstDe partments, 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,
etc.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQRfkDYechKq OuFEgEQKS4QCgpF XPp+mm54G8/3Hqcth/4KqhPcYAn3B8
5aCHhAmLHe5Ixce 2+wdHuHBd
=CGcN
-----END PGP SIGNATURE-----
Mark wrote:
[color=blue]
> How can I use the selections in a multi-select listbox as criteria in a query?
>[/color]
Comment