Using parameters with FIELD IN ([PARAMETER])

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

darkforcesjedi - 20 Jul 2006 16:35 GMT

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?

Reply to this Message

John Spencer - 20 Jul 2006 17:39 GMT

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)
[quoted text clipped - 12 lines]
> Is there a way to do this using parameters or must I generate the
> querystring in code?

Reply to this Message