Crosstab Query date range 1

[lbigk]

Hello,

I cannot figure out the issue with my query:

TRANSFORM Sum(tblProduction.ProdNbr) AS [The Value]
SELECT tblProduction.Task, tblProduction.UserID, Sum(tblProduction.ProdNbr) AS Total
FROM tblProduction
WHERE (((tblProduction.PDate) Between [Forms]![frmReports]![txtFrom] And [Forms]![frmReports]![txtTo]))
GROUP BY tblProduction.Task, tblProduction.UserID
PIVOT Format([PDate],"Short Date");

When I run this query, I get the following: "The Mircosoft Jet database engine does not recognize '[Forms]![frmReports]![txtFrom]' as a valid field name or expression."

If I use specific date range in the same WHERE statement, the query works correctly. The form with txtFrom and txtTo fields is loaded and fields are populated.

Any ideas?

Thank you.

 

[Remou]

You need to add a parameter. For example:

[tt]PARAMETERS [Forms]![frmReports]![txtFrom] DateTime,
[Forms]![frmReports]![txtTo] DateTime;
TRANSFORM Sum(tblProduction.ProdNbr) AS [The Value]
SELECT tblProduction.Task, tblProduction.UserID, Sum(tblProduction.ProdNbr) AS Total
FROM tblProduction
WHERE (((tblProduction.PDate) Between [Forms]![frmReports]![txtFrom] And [Forms]![frmReports]![txtTo]))
GROUP BY tblProduction.Task, tblProduction.UserID
PIVOT Format([PDate],"Short Date");[/tt]

 

[lbigk]

Remou,

Thank you soooo much, it worked perfectly.