How to call VBA-function from inside sql-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

As you have stated that you're using Access, then (out of the top of my head) yes, it's possible to use VBA functions in queries.

Check on the documentation and MSDN.

Answer 2

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)

Answer 3

Why not adding those headings in a table and join that table in your xtab query ?
That's probably easier to maintain that hard coding it in a function.