Access 2007 query: How to use a PARAMETERS variable in an SQL IN clause
Using Access 2007 in ANSI-92 mode. I would like to use a PARAMETER variable as the value of an IN clause. Something like:
Executing the query should allow me to enter a comma-separated list of values, such as "1,3,5,7"
Is that possible and, if so, what is the correct query syntax? COLUMN_A could be any datatype, depending on its containing table and query.
If this is not possible, any other suggestions?
I could do an "... IN (SELECT ...." sub-query in place of "... IN ([INVALUES])", but the data which is the target for the subquery is not well-tagged. The set of values for a particular IN clause is well-known by the end-users and is constant - but there are twenty-five or so different sets of such values (i.e., 25 different IN(a,b,c,...) combinations, with each individual value appearing in more than one of those combinations). I could "name" each combination, and then create a table holding a value-name pairing and do the subquery SELECTing from that new table but, as time is pressing and the combinations are well-known and static, I was hoping to get away with this implementation for now, then return for a better implementation later on (yes, I know, I know...)
Ideas appreciated. Thanks for stopping by and helping.
/R
PARAMETERS INVALUES;
SELECT * FROM TABLE WHERE COLUMN_A IN ([INVALUES]);Executing the query should allow me to enter a comma-separated list of values, such as "1,3,5,7"
Is that possible and, if so, what is the correct query syntax? COLUMN_A could be any datatype, depending on its containing table and query.
If this is not possible, any other suggestions?
I could do an "... IN (SELECT ...." sub-query in place of "... IN ([INVALUES])", but the data which is the target for the subquery is not well-tagged. The set of values for a particular IN clause is well-known by the end-users and is constant - but there are twenty-five or so different sets of such values (i.e., 25 different IN(a,b,c,...) combinations, with each individual value appearing in more than one of those combinations). I could "name" each combination, and then create a table holding a value-name pairing and do the subquery SELECTing from that new table but, as time is pressing and the combinations are well-known and static, I was hoping to get away with this implementation for now, then return for a better implementation later on (yes, I know, I know...)
Ideas appreciated. Thanks for stopping by and helping.
/R
I've tried this before, but is seems you can only enter in one value. As soon as you separate values with a comma, as you normally would, you get zero results. No error, but zero results.
mx
mx
As sort of a work around, you can do this ... within reason:
SELECT Table1.ID
FROM Table1
WHERE (((Table1.FIELD4)=[Enter Item 1] Or (Table1.FIELD4)=[Enter Item 2] Or (Table1.FIELD4)=[Enter Item 3]));
mx
SELECT Table1.ID
FROM Table1
WHERE (((Table1.FIELD4)=[Enter Item 1] Or (Table1.FIELD4)=[Enter Item 2] Or (Table1.FIELD4)=[Enter Item 3]));
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can try this:
SELECT * FROM TABLE WHERE Eval('" & COLUMN_A & "' IN ('" & Replace([Invalues],",","',
Where "Invalues" is the parameter containing your comma separated list. This will perform all comparisons as string comparisons.
SELECT * FROM TABLE WHERE Eval('" & COLUMN_A & "' IN ('" & Replace([Invalues],",","',
Where "Invalues" is the parameter containing your comma separated list. This will perform all comparisons as string comparisons.
Erm... my mistake:
SELECT * FROM TABLE WHERE Eval("'" & [COLUMN_A] & "' IN '" & Replace([Invalues],",","',
SELECT * FROM TABLE WHERE Eval("'" & [COLUMN_A] & "' IN '" & Replace([Invalues],",","',
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's a cool trick Shane. I fooled around with Eval() for a bit and got close ... but yours doe work.
Never say die!
mx
Never say die!
mx
Thanks, MX. I haven't lost my mojo yet. :)
Yeah, not exactly :-)
ASKER
Thank you to both matthewspatrick and shanesuebsahakarn. Each solution has its positives, and I now have two variations on the same theme to use.
What I would probably do is have a text box on a form to input the IN members, and then a command button on the form to launch the query. I'd then use VBA against the button's click event to modify the SQL statement for the QueryDef on the fly, and then open the query.