VBA to create crosstab style report with multiple values (1 Viewer)

[Blitznb]

I need help please, I have fooled with this scenario for months now and seem to have hit a wall.

I my db uses forms like northwinds 07 (I believe) in which you select the "grouping" fields such as customer, location, employee etc.

I am attempting to be able to pass these parameters through VBA (strSQL =) into a report. I am looking for a report that would produce sales by quarter. The hitch is I need two crosstab values, "Sales" & "Commission".

I followed Allen Brownes crosstab example to get "Multiple sets of Values" this works perfectly as a query and inturn I could create a form based on this same crosstab. The problem Is I can't seem to get this to work in VBA when I set Me.Recordsource=strSQL.

The SQL that works in SQL View of query designer is as follows.

TRANSFORM CCur(Nz(Sum(IIf([FieldName]="Sales",[Sales],[Commission])))) AS TheValue
SELECT [Sales Analysis].[Principal], [Sales Analysis].[Customer Name]
FROM tblXtabColumns, [Sales Analysis]
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",([Order Date]));

I am looking to pass parameters through "Sales Report Dialog" to open report "Quarterly Sales Report". In the reports code I tried to pass the parameters (The parameters are pass from Form code to report by "TempVars![FieldName]. ) and SQL as follows

strSQL = "TRANSFORM CCur(Nz(Sum(IIf([FieldName]=""Sales"",Nz([Sales],0),Nz([Commission],0))))) AS TheValue"
strSQL = strSQL & " SELECT [SalesGroupingField1], [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 [FieldName] & [Quarter];"

After fooling with this for weeks I decided to try and get "standard" values and fields to work and therefore changed to the following

strSQL = "TRANSFORM CCur(Nz(Sum(IIf([FieldName]='Sales',Sales,Commission)))) AS TheValue"
strSQL = strSQL & " SELECT [Sales Analysis].[Principal], [Sales Analysis].[Customer Name]"
strSQL = strSQL & " FROM tblXtabColumns, [Sales Analysis]"
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] & DatePart(""q"",([Order Date]));"

This still does not work properly, I continually get errors. The odd thing is if I create a query, go to SQL View and paste the ?strSQL = I get from the Immediate window, the query works perfectly and will work as the recordsource for the report.

Is it possible to pass parameters through a SQL statement into a reports record source that will give two values in a crosstab style?

Any suggestions on what I am doing wrong?

 

[Blitznb]

I finally got this to work, yippieeeeeeeeeeeeeeeeeeeeeeeeeeee.
Happy new year to me.