Hi,

I am using a multiple selection list box to obtain criteria/parameters for a query. Once the users clicks a command button, the selections made in the list box are written to a string. Then, in a SELECT statement, I used this string in a set comparison in order to determine if the row meets the criteria.

But I just can't seem to get the query to accept the parameter. I initially tried to do with the DoCmd.RunSQL.. but that only executes action queries. I have tried several other things, to no avail.

If anyone has any ideas, I would greatly appreciate it!

Thanks!

Two methods:

One: Build a sql string based upon criteria set via controls on your form

Two: Use controls on your form as criteria for your query. Caution: for combo boxes, the displayed valued may not be the bound value for a combo box control.

The problem with manually creating the SQL statement in a text string is that I am unsure how to execute it once it is built. DoCmd.RunSQL REQUIRES an action query, so a SELECT won't cut it (unless I make a temp table, which is hacky). If I knew how to execute ANY SQL statement, I would be all set! (So if you have any tips... ;-) )

Dim varItem As Variant
Dim strWhere As String

On Error GoTo Error_Handler

For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere = strWhere & "YourFieldName =" _
& Chr(39) & Me![YourListBoxName].Column(0, varItem) & Chr(39) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

DoCmd.OpenForm "YourFormName", , , strWhere

Put your field and List box names in the appropriate places and I think this will do what you want.