ms access - Call function for specifying columns in SQL TRANSFORM query

Question

Here is my query:

PARAMETERS ...
TRANSFORM ...
SELECT ...
...
PIVOT Mytable.type In ("Other","Info");

This is a cross query and I need to set all the column headings with this row: PIVOT Mytable.type In ("Other","Info") and now I have hard-coded the headings, Other and Info.

But I want to do this dynamically. So what I want to do is to call a vba-function that returns all the headings I need.

Something like this:

PIVOT Mytable.type In (myVbaFunction());

So my question is: How to call a vba-function inside the sql-query?

Answer 1

Yes, it is possible.
However, I don't think it's possible with WHERE IN (...).

Here is an example for a normal WHERE query:

Public Function Test() As String
    Test = "Smith"
End Function

...and then:

SELECT * FROM Users WHERE Name = Test();

It works, as long as the function only returns one value.
But I think it's not possible to let your function return something like "Smith, Miller" and use that like:

SELECT * FROM Users WHERE Name In (Test());

(at least I don't know how to do it)