How to pass arguments from an Access form to SQL Query with "IN" clause?

Question

I'm having trouble getting this query to display any results.

SELECT Evaluation_Table.*
FROM Evaluation_Table
WHERE (Evaluation_Table.Test_ID 
In ([Forms]![Test Data]![Group_Test_IDs]));

The control, [Group_Test_IDs] is a textbox that contains a string of IDs. For example it just contains numbers separated by commas: 1,2,3,4,5. While debugging, If I changed the query to look like this, it properly returns records:

SELECT Evaluation_Table.*
FROM Evaluation_Table
WHERE (Evaluation_Table.Test_ID 
In (1,2,3,4,5));

I can't seem to find the proper syntax. SQL in Access can sometimes be weird.

you could try having them enter the IDs into a table instead of a textbox and then doing a join — Beth
– Beth, Commented Jun 25, 2015 at 16:53

Gustav · Accepted Answer · 2015-06-25 16:35:33Z

2

I can't seem to find the proper syntax.

That's because there is none.

The IN selection cannot be dynamic; your only option is to rewrite the SQL via VBA.

answered Jun 25, 2015 at 16:35

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3657834 Over a year ago

I just discovered the query will work if the textbox contains a single number. example: 3. So the control can be passed. The purpose of the IN clause is to be able to find records in a list. My syntax for the IN clause itself is correct. For some reason I'm unable to send multiple values separated by commas.

Gustav Over a year ago

Well, that's exactly the issue. One value will do, a list will not. Neither will a function. You will have to write out the list and somehow insert or append it to the SQL without the list.

user3657834 Over a year ago

gotchya. I ended up working around this by using vb as you originally suggested. Thanks for the insight.

Collectives™ on Stack Overflow

How to pass arguments from an Access form to SQL Query with "IN" clause?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related