Hi everyone,



I have a multi-select list box on one of my forms.  I want to pass the 

selected items to stored query as a parameter.  I know how to concatenate 

all the selected items, however, I can not work out how to get the query 

to accept the concatenated string as a parameter.  For example, we have 

various grant applicants in our database, ie. A- Area, C- CERP, U - 

University and so on.  I need to open a stored query base the applicants 

selected in the multi-select list box. 



I tried concatenating selected applicants and storing the concatenated 

string in the text box called txtApplicants  and then referring to 

txtApplicants in query criteria.  For instance, if the user selects A and 

U list items, I would store "A", "U" in the textbox and refer to it in the 

criteria using "IN" statement, ie. IN ([forms]![frmGrants]![txtApplicants])

. 



Normally if I type IN ("A", "U") in the criteria box I would get a certain 

number of records, however, referring to a form field generates an empty 

recordset.  I also tried using LIKE statement without success.



I would appreciate any help I can get. 



PS: I took a look at an example posted by Dev Ashish on The access Web 

(httt://www.mvps.org/access/forms/frm0007.htm), which discussed this 

topic. 



He actually did have a note stating that his method can be used in a 

parameterized query provided I pass the entire Where clause to it via code 

as a parameter.  The question remains, how do I pass the entire Where 

clause to parameterized query via code as a parameter?



Following is the article from httt://www.mvps.org/access/forms/frm0007.htm.

 Could any one let me know how to achive my objective?  Better still, if 

you work it out using a database, could you send me the solution database 

to have a look it at john.fejsa@h...  Much appreciated

.



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D

(Q) I have a MultiSelect listbox control on my form. I want to pass the 

selected items to a query as a parameter. How do I do this?



(A) Unlike simple listbox controls which can be referenced as a parameter 

by a query, MultiSelect listboxes cannot be used directly as a parameter. 

This is because calling the listbox (Forms!frmMyForm!lbMultiSelListBox) 

from anywhere will not automatically concatenate all the selected items. 

You need to build the criteria yourself.



Note: You can still use a parameterized query provided you pass the entire 

Where clause to it via code as a parameter. (eg. Have the query reference 

a hidden control to which you manually assign the complete WHERE clause 

using the following logic.)



For example,

'******************** Code Start ************************

Dim frm As Form, ctl As Control

Dim varItem As Variant

Dim strSQL As String

    Set frm =3D Form!frmMyForm

    Set ctl =3D frm!lbMultiSelectListbox

    strSQL =3D "Select * from Employees where [EmpID]=3D"

    'Assuming long [EmpID] is the bound field in lb

    'enumerate selected items and

    'concatenate to strSQL

    For Each varItem In ctl.ItemsSelected

        strSQL =3D strSQL & ctl.ItemData(varItem) & " OR [EmpID]=3D"

    Next varItem



    'Trim the end of strSQL

    strSQL=3Dleft$(strSQL,len(strSQL)-12))

'******************** Code end ************************

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D



Thanks in advance



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

john.fejsa@h...



This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient,
please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily
the views of Hunter Health.