Passing a string into a query criteria

[jonohara]

I need to run a query, the criteria for which, I hope to pass in from a string variable on a form.

i.e. I have a string variable e.g. 'ref1, ref2, ref3' (built up from a multiselect listbox) The query has a corresponding reference field. Can I set the criteria for the query using this variable? (it currently only works when the string contains a single item e.g. 'ref1').

I would prefer not to have to create a query on the fly if it can be avoided.

Thanks

 

[gol4]

This will work if you build your string correctly and use the in clause

because you are not using numbers you need to use the single quote around each so your string looks like
strcrit ="'ref1', 'ref2', 'ref3'"
then you can say "where this in (" & strcrit & &quot"

If ref1 and ref2 are field names then you would do more like
strcrit ="ref1, ref2, ref3 "
srtsql = "select " & strcrit & "from tblname"

 

[biggoober]

Create a table called "criteria", with columns corresponding to the values you wish to pass. (example: Customer, Date, Item)
In your code when you wish your query to run, clear this table, then write what you want to pass to the query. Have your query use this table .