M
mike
How do I take the selected values of a multiselect list
box and use them as filter criteria in a query
box and use them as filter criteria in a query
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
MGFoster
mike said:How do I take the selected values of a multiselect list
box and use them as filter criteria in a query
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I use the following code to extract the selected values in a multi
select ListBox. The return string can be used in the WHERE clause of a
query or in a form/report Filter property. E.g. (assumes there is
something selected in ListBox):
Me.Filter = "ID In (" & getIds(lstMyListBox, dbNumeric) & ")"
To put in a query (using VBA - strSQL holds 1st part of query):
strSQL = strSQL & "WHERE ID In (" & getIds(lstMyListBox, _
dbNumeric) & ")"
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
- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQF99/oechKqOuFEgEQL8uQCeJINf9kWz6ZZyrnG4R/vYl+PIzKkAnRiO
39PfoYF0Q5+dYLzFlJFoJPN7
=hl1d
-----END PGP SIGNATURE-----
G
Guest
Hi i need Help
I am actually working with ms Access and I am using a form
to enable the end user to select Multiple item that I will
use as a criteria for a query .
Whenever I tried to do it programmatically I recevied the
error -2147217900 (Syntax Error Missing Operator. In fact
I copy the sql View in Ms Access and parse it into
string....
When I try to call the function from the query grid I
received Runtime Error 2001 " You cancelled the previous
operation.
Anyway, now what I want is how to use the form with
listbox multi select property set to extend and pass it
directly to the query thru the query builder in Ms Access.
thank you for your hekp
Please reply to (e-mail address removed)
I am actually working with ms Access and I am using a form
to enable the end user to select Multiple item that I will
use as a criteria for a query .
Whenever I tried to do it programmatically I recevied the
error -2147217900 (Syntax Error Missing Operator. In fact
I copy the sql View in Ms Access and parse it into
string....
When I try to call the function from the query grid I
received Runtime Error 2001 " You cancelled the previous
operation.
Anyway, now what I want is how to use the form with
listbox multi select property set to extend and pass it
directly to the query thru the query builder in Ms Access.
thank you for your hekp
Please reply to (e-mail address removed)
J
John Vinson
Anyway, now what I want is how to use the form with
listbox multi select property set to extend and pass it
directly to the query thru the query builder in Ms Access.
You cannot do so. A multiselect Listbox does not HAVE a value at all,
much less a list of values!
What you will need to do instead is write some VBA code to loop
through the listbox's ItemsSelected collection and construct a SQL
string. See for example
http://www.mvps.org/access/reports/rpt0005.htm