how would i setup a list box with multiselection to run a query

[mark]

i have a query that i need multiple inputs from a form. I know i should use
mult-selection in the form, but how do i transfer my selections to the form

 

[Paul Overway]

You need to iterate through the ItemsSelected collection of the listbox and
get all the selected items. Then you need to either build and assign SQL
for the query, i.e.,

Dim varSelected As Variant
Dim strWhere as String
Dim strSQL as string
Dim db as database
Dim qdf as querydef

strSQL = "SELECT * FROM SomeTable"

strWhere = " WHERE [SomeField] In("

For each varSelected in Me.lstSomeList.ItemsSelected
strWhere = Me.lstSomeList.ItemData(varSelected) & ","
Next

strWhere = Left(strWhere,Len(strWhere)-1) & ")"

strSQL = strSQL & strWHERE

Set db = currentdb()
Set qdf = db.QueryDefs("SomeQuery")

qdf.SQL = strSQL



....or assign the list of selected items to a hidden text box in a comma
separated format and then use Eval in your query like this,i.e.,

SELECT * FROM SomeTable WHERE Eval([SomeField] & " In(" &
Forms![SomeForm]![HiddenTextBox] & ")")=True

*this second method will not work if you have Jet Sandbox turned
on....normally, this would only be an potential issue for Access 2003

If the listbox items are text, you need to enclose each item in single
quotes for both method, i..e, the In parameter should look like..

'Item1','Item2','Item3'