IN Operator - parameter query

[razorking]

I know how to make a parameter query - to prompt the user for a value. And I know how to create a query using In: In("red","blue","yellow"). Is there a way to have a parameter query that lets a user input multiple values, maybe sperated by a comma?

 

[Banana]

No. It's a common question, and understandably so. What is missed is that IN operator actually does not mean "parse whatever is passed in this" but rather acts as a compile-time shortcut for translating color IN ("red", "yellow", "blue") to this:

color = "red" OR color = "yellow" OR color = "blue"

Because that's done at the compile-time (e.g. when you save query), this construct:

color IN ([MyParam])

is now:

color = [MyParam]

so when you try to run the parameter query with the same input, it reads as:

color = """red"",""blue"",""yellow"""

Obviously, this won't work!

A possible workaround is to construct a temporary table and join this to the original table:

SELECT colors.*
FROM colors
JOIN tmpSelection
ON colors.colorID = tmpSelection.colorID

There are other workarounds, too... depends what is the specific problem you want to achieve here.

HTH.

 

[raskew]

Hi -

Take a look at this from the Code Depository. It might be helful.

Best wishes - Bob

 

[ajetrumpet]

I know how to make a parameter query - to prompt the user for a value. And I know how to create a query using In: In("red","blue","yellow"). Is there a way to have a parameter query that lets a user input multiple values, maybe sperated by a comma?

in my opinion, a better workaround would be to use the OR operator and put the selections on a form in a nice neat way that can be orderly to the user.