Using parameters with FIELD IN ([PARAMETER])

[darkforcesjedi]

I have a query that I want to parameterize so that I can display data
for any number of selected `Types` of components. My query (abridged)
looks like:

SELECT * FROM Subcomponents WHERE TypeID IN (55,56,58)

That's fine and dandy but if I use a parameter for it "TypeID IN
([Param1])" and input 55,56,58 in the popup that Access generates, I
get a blank recordset back. I want to be able to create the list of
TypeIDs in code and pass it to the query using DAO when I open the
recordset. The number of TypeIDs selected will vary such that using 3
parameters like "TypeID IN ([Param1],[Param2],[Param3])" will not be an
option.

Is there a way to do this using parameters or must I generate the
querystring in code?

 

[John Spencer]

You are better off doing this using VBA (in my opinion), but the following
should work

SELECT *
FROM subComponent
WHERE Instr(1,"," & [Enter Types] & ",", "," & TypeID & ",") > 0

If you want you could strip out any spaces by using the replace function
(Access 2000 SP3 or later) to take care of typing errors

SELECT *
FROM subComponent
WHERE Instr(1,"," & Replace([Enter Types]," ","") & ",", "," & TypeID & ",")

0

I have a query that I want to parameterize so that I can display data
for any number of selected `Types` of components. My query (abridged)
looks like:

SELECT * FROM Subcomponents WHERE TypeID IN (55,56,58)

That's fine and dandy but if I use a parameter for it "TypeID IN
([Param1])" and input 55,56,58 in the popup that Access generates, I
get a blank recordset back. I want to be able to create the list of
TypeIDs in code and pass it to the query using DAO when I open the
recordset. The number of TypeIDs selected will vary such that using 3
parameters like "TypeID IN ([Param1],[Param2],[Param3])" will not be an
option.

Is there a way to do this using parameters or must I generate the
querystring in code?