Using date to specify field name to use

[abbarition]

I have just inherited several databases that have been set up with year names
as part of the field names (2007Total, 2008Total, 2009Total, etc.). Each
year they add additional fields for the new year. There is no time right now
to change the setup of these tables, so I just need to write a query to
handle these items for the unforseen future.

Is there a way to write an IIf statement in a query that will decide which
field to use based off the date of another field. For example, if the year
of my date field is 2007, it should use the 2007Total field, if the year is
2008, the 2008Total field. I am trying to avoid writing out and iif
statement saying IIf(Year([DateField])=2007,[2007Total]) because I'm afraid
they will forget to update the statement for new years into every query.

Is it possible to write a statement in the query (or in VBA) that will help
me accomplish this?

Thanks!

 

[KARL DEWEY]

I do not think it can be done. You got to fix the database table structure.
Use a union query.
SELECT Field1, Field2, "2007" AS TotalYear, 2007Total AS Value
FROM YourTable
UNION ALL SELECT Field1, Field2, "2008" AS TotalYear, 2008Total AS Value
FROM YourTable
UNION ALL SELECT Field1, Field2, "2009" AS TotalYear, 2009Total AS Value
FROM YourTable;