Trouble with Crosstab Query 3

[jdgreen]

I have a crosstab query that is based on another query (qryLMPMbyDay). The code for qryLMPMbyDay is:

SELECT tblLMPM.Plant, Sum(tblLMPM.Required) AS SumOfRequired, Sum(tblLMPM.Shipped) AS SumOfShipped, tblLMPM.Cause, tblLMPM.Date
FROM tblLMPM
WHERE (((tblLMPM.Date) Between [Forms]![frmMain]![frmReports].[Form]![Text21] And [Forms]![frmMain]![frmReports].[Form]![Text23]))
GROUP BY tblLMPM.Plant, tblLMPM.Cause, tblLMPM.Date;

It takes dates entered on a form and uses them for the criteria. This query works perfectly, but when I run a crosstab query based on this query, I get an error that "The Microsoft Jet database engine does not recognize '[Forms]![frmMain]![frmReports].[Form]![Text21]' as a valid field name or expression.
Here is that query:

TRANSFORM Sum(qryLMPMbyDay.SumOfRequired) AS SumOfSumOfRequired
SELECT qryLMPMbyDay.Plant, Sum(qryLMPMbyDay.SumOfRequired) AS [Total Of SumOfRequired]
FROM qryLMPMbyDay
GROUP BY qryLMPMbyDay.Plant
PIVOT Format([Date],"Long Date");

I have tried basing the crosstab query directly on the table and adding in the criteria there with the same result. I haven't used crosstab queries much so I'm confused.


John Green

 

[PHV]

Add this 2 lines before the TRANSFORM:
PARAMETERS [Forms]![frmMain]![frmReports].[Form]![Text21] DateTime,
[Forms]![frmMain]![frmReports].[Form]![Text23] DateTime;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jdgreen]

Worked like a charm.

John Green