View Single Post
  #4 (permalink)  
Old May 31st, 2011, 12:02 PM
Odeh Naber Odeh Naber is offline
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Ok folks! I am really close now! :) The following crosstab query gives me what I need but for all years - how can I make it select only the chosen year??

TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
GROUP BY Year([MonthEnding]), qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

I tried the following:


TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
WHERE (((Year([MonthEnding]))=Year([Forms]![frmSelectParticulars]![cboSelectMonthYear])))
GROUP BY Year([MonthEnding]), qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

and also the following:


TRANSFORM First(qryCompleteList.RevenueValue) AS FirstOfRevenueValue
SELECT Year([MonthEnding]) AS STYear, qryCompleteList.RevenueCategory
FROM qryCompleteList
WHERE (((Year([MonthEnding]))=Year([Forms]![frmSelectParticulars]![cboSelectMonthYear])))
GROUP BY qryCompleteList.RevenueCategory
ORDER BY Year([MonthEnding])
PIVOT Format([MonthEnding],"mmmm");

but it gives me an error saying that the MS Office Database Engine does not recognise [Forms]![frmSelectParticulars]![cboSelectMonthYear] as a valid field name or expression. It is typed in perfectly - no mistakes so I am not sure where I am going wrong?

Thank you!!!
Reply With Quote