query filter using dynamically populated IN() function

[dsatino]

I have a query that's filtered like this:

WHERE [myfield] IN(myfunction())

if myfunction() returns only a single value, the query operates as expected. But if it returns multiple values then it returns nothing.

For example, if myfunction() returns 4 then I get results. If myfunction() returns 4,7 then it returns nothing.

I've manually changed the where statement to:
[myfield] IN(4,7) and it works fine. I've tested my function() and it returns 4,7.

It seems this should work, but it doesn't. Any ideas why. My guess is that the function is being interpreted after the SQL statement and that the 4,7 is being viewed as a single item.

[NeoPa]

Spot on. I suppose the function returns a string value. Consider what you'd expect if the value were substituted into the SQL directly :

WHERE [myfield] IN ('4,7')

It's a single string value, not a couple of numeric values. For that you'd need to call the function before formulating the SQL string :

strSQL = "... " & _
         "WHERE [MyField] In(" & MyFunction() & ")"