CODEWITHSUNDEEP
ms-access
IAmBatman
IAmBatman

Reputation: 687

MS Access: Referring to a dynamic column name?

Two of the columns in Query A are labeled 2012 and 2011.

However, next year, the columns will be 2013 and 2012. This is because those columns are part of a crosstab where the name is done via the year() function.

Anyway, I am using a new query to add a column that subtracts the values in the two year columns, but I don't know how to refer to those columns dynamically.

e.g. I could easily add a column 

Difference: [2012 Revenue] - [2011 Revenue]

But this would stop working next year.

Upvotes: 3

Views: 3559

Answers (2)

iDevlop
iDevlop

Reputation: 25252

Building on HelloW excellent suggestion, you could use as the Column Header of your Crosstab an expression like "RevYr" & (Year(Date())-Year(RevenueDate), which would evaluate to
RevYr0, RevYr1, RevYr2, etc...
Your difference would then become 

Difference: RevYr0 - RevYr1

Edit:
Facing a similar problem using a crosstab, I found these 2 very interesting links:
http://allenbrowne.com/ser-67.html
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

Upvotes: 1

HelloW
HelloW

Reputation: 1617

Why don't you label them these columns to something a little more generic and use them like this
Difference:[This Year] - [Last Year]

Upvotes: 4

Related Questions