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!

I just figured out the solution regarding the CrossTab Query!

I created a Public Function GETYEAR:

Public Function GetYear(FormName As String, FieldName As String)
GetYear = Forms(FormName).Controls(FieldName)
End Function

And then modified the line in the crosstab query to:

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

And it worked! Now just to figure out how to sort the months on the top in the correct order.