Setting crosstab headings dynamically

[dougie]

I have a crosstab based on a query. Depending on the selection criteria used in the base query the crosstab will generate different column headings, predictable in advance. For example if the user chooses 'Fruit' the crosstab column headings will be 'orange, apple, pear' and if he chooses 'Vegetable' they will be 'onion, potato, carrot'. Can I set these column headings dynamically with VBA so that I can use just one crosstab for all user selections? Creating a different crosstab for each possible user selection and hard-coding the column headings in the crosstab properties isn't a good idea as the fruits might not always be 'orange, apple, pear'.



I'm using Access 2007.

[June7]

If this can be done, I am guessing would require modifying query with QueryDef class properties.

[dougie]

Sussed it. You write VBA to append an 'IN' clause to the SQL string of the crosstab. Here's the SQL constructed by code:

TRANSFORM Sum(qryManufNPReason.NP) AS SumOfNP
SELECT qryManufNPReason.Department, Sum(qryManufNPReason.NP) AS [Total Of NP]
FROM qryManufNPReason
GROUP BY qryManufNPReason.Department
PIVOT qryManufNPReason.NPReasonDescription In ('Supervision','New Product','Testing','Rechargeable','Laser','Tool setting','R&D');

[June7]

Ah, I was assuming a saved Access query object. I have never needed crosstabs and never done in VBA. Will have to keep this in mind. Thanks for posting solution.