Crosstab Questions

[Paul H]

I'm trying to write what I though would be a simple Cross Tab query and I'm getting this error.

Microsoft Access database engine does not recognize [Forms]![frm_Chart]![txt_FromDate] as a valid field name or expression.

[CODE]PARAMETERS [Forms]![frm_Chart]![txt_FromDate] DateTime, [ [Forms]]![frm_Chart]![txt_TillDate] DateTime;
TRANSFORM Sum(tbl_Production.Production) AS SumOfProduction
SELECT tbl_Production.[MOD], tbl_Production.Machine, tbl_Production.Shift
FROM tbl_Production INNER JOIN tbl_Shift ON tbl_Production.Shift = tbl_Shift.Shift
WHERE (((tbl_Production.ProductionDate) Between CDate([Forms]![frm_Chart]![txt_FromDate]) And CDate([Forms]![frm_Chart]![txt_TillDate])))
GROUP BY tbl_Production.[MOD], tbl_Production.Machine, tbl_Shift.Shift_Order, tbl_Production.Shift
PIVOT tbl_Production.ProductionDate;
CODE]



I've run the code using the #7/1/2012# format and it runs fine. I also get the same error when I try to use an ordinary parameter prompt, i.e. [From Date] and [Till Date]

[June7]

I have never used Parameters clause in a crosstab but then I don't do many crosstabs.

Review: http://allenbrowne.com/ser-67.html

[Paul H]

The error occurs whether I declare the Parameters or not. Declaring them was an unsuccessful attempt to solve the problem. The crux of the matter is in the WHERE clause. This works:

WHERE (((tbl_Production.ProductionDate) Between #11/7/2012# And #11/13/2012#))

and this doesn't.

Between CDate([Forms]![frm_Chart]![txt_FromDate]) And CDate([Forms]![frm_Chart]![txt_TillDate])

I know the syntax is correct. I've tested it in a Select query.

[Paul H]

After repeated attempts, I finally got this right. There may, must, be a stray space or mark in my form parameter causing it not to find the specified text box. I just tested again and found this to be the case.
A space after the final bracket is invisible in the Query Parameters box,

[Forms]![frm_Chart]![txt_FromDate]
[Forms]![frm_Chart]![txt_TillDate]

but gets translated as this

PARAMETERS [Forms]![frm_Chart]![[txt_FromDate] ] DateTime, [Forms]![frm_Chart]![txt_TillDate] DateTime;

in the SQL window. Note the extra brackets. I now see this in the example I posted. Just another one of those surprises from Microsoft Access.

Happy New Year