Please help using variables in crosstabs

[Blitznb]

I am attemtping to add additional column headings to create a crosstab query. I have worked out the scenario to do this through query design and then based a report on same query. I am now trying to assign variables through a form. I feel as though I have tried every option and still get errors. I fear it may not be possible, however if you can create the query (Check) and you can create the report (check) and you can assign variables you have to be able to do the same with in crosstab report/query, right?

I am basically using the following query as the basis for my report and sql variable design.

TRANSFORM CCur(Nz(Sum(IIf([FieldName]="Sales",Nz([Sales],0),Nz([Commission],0))))) AS TheValue
SELECT [Sales Analysis].Principal, [Sales Analysis].[customer name]
FROM tblXtabColumns, [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order ID]
WHERE (((Orders.[Order Date]) Between #1/1/2011# And #12/31/2011#))
GROUP BY [Sales Analysis].Principal, [Sales Analysis].[customer name]
PIVOT [FieldName] & DatePart("q",([Orders].[Order Date]));

To get the extra columns for the crosstab I created a table = tblXtabColumns which has two fields = Sales & Commission. This aligns with the queries true source and equals the same field names for the values I want.

I believe this is what is causing the error. This is the SQL from my VBA

strSQL = "TRANSFORM CCur(Nz(Sum(IIf([FieldName]=[Sales],Nz([Sales],0),Nz([Commission],0))))) As TheValue"
strSQL = strSQL & " SELECT [" & TempVars![Display1] & "] As SalesGroupingField1, [" & TempVars![Display] & "] As SalesGroupingField"
strSQL = strSQL & " FROM tblXtabColumns, [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order ID]"
strSQL = strSQL & " Where [Quarter]=" & TempVars![Quarter] & " AND [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY SalesGroupingField1, SalesGroupingField"
strSQL = strSQL & " Pivot [tblXtabColumns].[FieldName] & [Sales Analysis].[MonthOfQuarter] In (1,2,3)"

TempVars!Display & 1 are values set from my "selection" form.
Quarter, Year, MonthofQuarter are all fields in Query Sales Analysis.


Does anyone see the issue here? Is this even possible? I feel it must be.......

 

[Blitznb]

Is it possible to
1. Assign Dynamic Columns to a crosstab style report?
2. Pass variabless to same?
3. Dynamically assign values to same?

If you can create the query and the report you have to be able to pass variables through to this kind of report, right? Urgh.........

 

[Blitznb]

Can someone please help?

Why is it this code works in query SQL View, but not as VBA string named strSQL?

    strSQL = "TRANSFORM CCur(Nz(Sum(IIf([FieldName]=""Sales"",Nz([Sales],0),Nz([Commission],0))))) AS TheValue"
    strSQL = strSQL & " SELECT [Sales Analysis].Principal, [Sales Analysis].[customer name]"
    strSQL = strSQL & " FROM tblXtabColumns, [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order ID]"
    strSQL = strSQL & " WHERE (((Orders.[Order Date]) Between #1/1/2011# And #12/31/2011#))"
    strSQL = strSQL & " GROUP BY [Sales Analysis].Principal, [Sales Analysis].[customer name]"
    strSQL = strSQL & " PIVOT [FieldName] & [MonthOfQuarter];"

Currently I am getting Error 3637
Cannot use the crosstab of a non-fixed column as a subquery.