IN string syntax in Crosstab query

[Robert Burns]

Hello there,

I am trying to force the column headers of a crosstab query by setting the IN string of the query through VBA, rather than setting the column headers property in the query design window(though I would still have the same problem, I think, if I tried to set the column headers property from code!).

My problems are:

1.How can I set the IN string to show (full) month names for the 6 months up to last month?
2.What is the correct syntax to use with the IN string?

The best I have managed is below, but this produces the error 'Missing ), ], or Item in query expression' related to the IN string.


TRANSFORM Sum(tblContracts.TotalQuantity) AS SumOfTotalQuantity
SELECT tblSpecies.WoodSpecies
FROM tblSpecies INNER JOIN tblContracts ON tblSpecies.SpeciesID = tblContracts.Species
GROUP BY tblSpecies.WoodSpecies
ORDER BY Format([ContractDate],"mmmm") DESC
PIVOT Format([ContractDate],"mmmm")

IN (MonthName(Format(Date, "m") - 6) , MonthName(Format(Date, "m") - 5),
MonthName(Format(Date, "m") - 4), MonthName(Format(Date, "m") - 3),
MonthName(Format(Date, "m") - 2), MonthName(Format(Date, "m") - 1);

I appreciate that I may have the date bit wrong and I'm sure my syntax is ropey,

Any help much appreciated,

Rob.