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

filtering a query by values in multi-select list box ?

P: n/a
Hello.

How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?

Regards,

Zlatko
Jan 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
A query cannot read the values in a multi-select list box.

It is possible to write a VBA function that loops through the ItemsSelected
collection, and returns True if the value in the record is one of them (True
becoming the result of a condition in the WHERE clause), but it's messy.

What is the target for this query? If it is for a report, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

If it is for a form, you could create the Filter string for the form in
exactly the same way as that article creates the WhereCondition for
OpenReport.

If it is for a Recordset, again you could create the entire SQL string that
way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...

How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?

Regards,

Zlatko

Jan 17 '06 #2

P: n/a
OK. I solved it in this way: I wrote a module with two functions, first one
creates a string to be included inside IN expression of WHERE clause, and
second one to synchonize any "child" listbox in correspondence to "parent"
listbox.

Option Compare Database

Function MultiselectListValues(ListFullName As Control) As String
'This function returns a string, as comma-separated list of values,
'based on selected values in a multi-select list box.
'This string can be used inside IN expression in WHERE clause of a query

Dim strWhere As String, varItem As Variant

'Request to edit items selected in the list box
'If no items selected, then nothing to do
If ListFullName.ItemsSelected.Count = 0 Then Exit Function
'Loop through the items selected collection
For Each varItem In ListFullName.ItemsSelected
'Grab the column for each selected item
strWhere = strWhere & "'" & ListFullName.Column(0, varItem) & "'" & ","
Next varItem
'Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
'Finish creation of "IN" string
MultiselectListValues = strWhere

End Function

Function SynchronizeListBox(ParentListFullName As Control, ChildListFullName
As Control, SelectExpression As String, LinkField As String)
'This function synchronize recordset in child list box, based on selectde
values in parent list box.
'Parent list box has a query or SQL statement as its rowsource, while
recordset of child list box is created programmatically.

Dim db As Object
Dim qdf As Object
Dim qdfSQL As String
Dim rs As Object
Dim strWho As String
Dim strWhere As String
Dim strSQL As String

DoCmd.Hourglass True

Set db = CurrentDb

strWho = LinkField
strWhere = MultiselectListValues(ParentListFullName)

Set qdf = db.CreateQueryDef("")
If strWhere <> "" Then
strSQL = SelectExpression & " WHERE (((" & strWho & ")IN(" & strWhere &
")));"
Else
strSQL = "select plants.plant from plants WHERE " & strWho & "=""""" &
";"
End If
qdf.SQL = strSQL
qdf.returnsrecords = True
Set rs = qdf.openrecordset()

Set ChildListFullName.Recordset = rs.clone

rs.Close
qdf.Close

DoCmd.Hourglass False

End Function

"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:43***********************@per-qv1-newsreader-01.iinet.net.au...
A query cannot read the values in a multi-select list box.

It is possible to write a VBA function that loops through the
ItemsSelected collection, and returns True if the value in the record is
one of them (True becoming the result of a condition in the WHERE clause),
but it's messy.

What is the target for this query? If it is for a report, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

If it is for a form, you could create the Filter string for the form in
exactly the same way as that article creates the WhereCondition for
OpenReport.

If it is for a Recordset, again you could create the entire SQL string
that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...

How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?

Regards,

Zlatko



Jan 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.