I'm trying to pass some values from a vba function to an SQL.
This is my SQL
SELECT *
FROM Hierarchy3
WHERE ID IN (getList("1 and 2"));
this is the definition of the vba function:
Function getList(Measure As String) As String
when I call the function I get: 1,2,3 as a String.
if I run the SQL as
SELECT *
FROM Hierarchy3
WHERE ID IN (1,2,3);
it works fine, but combining the two doesn't work. So I guess the String type is wrong, can you please help?
Your problem is that your query does not work like you expect it. Your function returns a string, so the query performed is:
SELECT *
FROM Hierarchy3
WHERE ID IN ("1,2,3");
Notice the quotation marks. Basically you are comparing an integer to a string and so doesn't return any results.
What you can do is use the INSTR function to see if the ID can be found in the string:
SELECT *
FROM Hierarchy3
WHERE INSTR(getList("1 and 2"),ID);
Now it will work because ID 1 can be found in the string "1,2,3" so the INSTR function will return the position where it can be found.
