Hiya folks!

I am having a minor issue with the following CROSSTAB QUERY:

TRANSFORM First(BasicStatistics01.Measurement) AS FirstOfMeasurement
SELECT BasicStatistics01.Category
FROM BasicStatistics01
WHERE (((Format([MeasurementDate],"yyyy"))=2010))
GROUP BY BasicStatistics01.Category
ORDER BY Format([MeasurementDate],"mmmm yyyy")
PIVOT Format([MeasurementDate],"mmmm yyyy");


The above is working beautifully - so whenever I change the year, for example, from 2010 to 2009, the report adjusts to show me the months for that specific year. The above gives me something similar to the following type of report:

-------------------------January 2010--------February 2010--------March 2010--------
Measurement------10---------------25-----------------55

But if I change the WHERE line to:

WHERE (((Format([MeasurementDate],"yyyy"))=[Forms]![frmSelectYear]![SelectYear]))

so that it would be equal to the year inputted in a separate form, I get the following error:

"The Microsoft Office Access database engine does not recognise '[Forms]![frmSelectYear]![SelectYear]' as a valid field name or expression."

The field in the separate form is unbound and is configured as such:

SELECT DISTINCT Format([StatisticDate],"yyyy") AS SelectYear
FROM tblStatisticDate
ORDER BY Format([StatisticDate],"yyyy") DESC;

What am I doing wrong?

Another minor issue - the months across the top of the page are not in order from January on the left to December on the right. How can I order this by month in ascending order?

Thank you!

Did you try editing in Design View instead of SQL?
I've found that sometimes Access doesn't take kindly to directly editing the SQL statements.
Also, check to make sure your form field name is matching your SQL.

Good Luck.