Variable field name in query

[Fourstar]

I have a complicated report that uses 6 crosstab queries to build a master query that feeds a report. The report tracks data by month during a specific year. One of the sub-queries counts the number of permit applications and displays them by month (row) and year (column). (The fields available from this query in design view are Month and 2009.) The field name in the query is "Applied: [Monthly Summary Applications].[2009]" The master query works fine on the current year, because it is built using current year data. However, in 2010 someone will have to edit the master query, which I want to avoid. I would like to replace the [2009] with a variable. I have tried using the Iif() function like this:
IIf(Eval(Month(Date())=1 And Day(Date())<28),[Monthly Summary Applications].[Year(DateAdd("yyyy",-1,Date()))],[Monthly Summary Applications].[Year(Date())]) but Jet doesn't recognize the name as a valid field.
(The reason for the Iif() function is to get the last years complete data when the report is generated up until Jan. 28, at which time it generates data for the new year.)

Is there a simple solution to this?

Thanks in advance for any ideas!

 

[Dennisk]

I would turn it on its head and have the months as columns, as these can be fixed i.e. Jan - Dec

then each year's data would be on a separate row which can grow and grow.

 

[Fourstar]

I would turn it on its head and have the monthe as columns these can be fixed i.e. Jan - Dec then each year's data would be on a separate row which can grow and grow.

Thanks for the quick reply! I tried that, and it almost worked except that the other 5 queries in the report are cross-tab queries related by month so I ended up with no relational link for this query and I had 144 rows.

I may have to redesign the entire thing, which I am trying to avoid.