Use Multi-Select List boxes as query parameters

[123]

Thank you for your help:

I read this question in mvps site :

I take this code and convert it to function … and put this function in query
grid and this function not work:

My question is How to benfit form this code or how this code is work?

Thank you

--------------------------------------------------------------------------

Forms: Use Multi-Select List boxes as query parameters

--------------------------------------------------------------------------



(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 = Form!frmMyForm

Set ctl = frm!lbMultiSelectListbox

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

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

'enumerate selected items and

'concatenate to strSQL

For Each varItem In ctl.ItemsSelected

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

Next varItem



'Trim the end of strSQL

strSQL=left$(strSQL,len(strSQL)-12))

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



]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]





Function multiselect()

Dim frm As Form, ctl As Control

Dim varItem As Variant

Dim strSQL As String

Set frm = Application.Forms!frmreport

Set ctl = frm!s1

strSQL = "Select * from client where [Group]=
strSQL=left$(strSQL,len(strSQL)-12))"

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

'enumerate selected items and

'concatenate to strSQL

For Each varItem In ctl.ItemsSelected

strSQL = strSQL & ctl.ItemData(varItem) & " OR
[GroupNumber]=strSQL=left$(strSQL,len(strSQL)-12))"

Next varItem



'Trim the end of strSQL

'strSQL=left$(strSQL,len(strSQL)-12))



End Function