Crosstab column headings cause query to not have results

[jetersauce]

I tried to set column headings on a crosstab query so that I can display them on a report (since the column headings change depending on the OrderID). When I added the code in bold, it caused the query to not display any results in those columns

PIVOT Results.SampleNumber In ("Case 1","Case 2","Case 3");

What did I do wrong? The complete code of the query is:

PARAMETERS [Forms]![AFL_GeneralSummary]![txtOrderID] Text ( 255 );
TRANSFORM First([Results.Result] & " " & [Results.Units]) AS Results
SELECT CustomerParams.ProjectID, Results.Test, Results.Param, CustomerParams.LowerLimit, CustomerParams.UpperLimit
FROM (CustomerParams INNER JOIN Results ON (CustomerParams.Test = Results.Test) AND (CustomerParams.Param = Results.Param)) INNER JOIN Orders ON (Results.OrderID = Orders.OrderID) AND (CustomerParams.ProjectID = Orders.ProjectID) AND (CustomerParams.CustomerID = Orders.CustomerID)
WHERE (((Results.Test)="Defects" Or (Results.Test)="Defects % Compliance" Or (Results.Test)="Total Defects %" Or (Results.Test)="Total Defects") AND ((Results.OrderID)=[Forms]![AFL_GeneralSummary]![txtOrderID]) AND ((Results.SampleNumber) Not Like "*-04" And (Results.SampleNumber) Not Like "*-05"))
GROUP BY CustomerParams.ProjectID, Results.Test, Results.Param, CustomerParams.LowerLimit, CustomerParams.UpperLimit, Results.Report
ORDER BY Results.Test, Results.Param DESC 
PIVOT Results.SampleNumber In ("Case 1","Case 2","Case 3");

 

[apr pillai]

Check this out for a solution: Dynamic Report