Referencing a string as a variable

My question is something that I’ve been faced with a number of times over the past couple of years, but I’ve been able to get around it until now, I assume that is a fairly common question but I’ve searched MSDN and other sources for help, but to no avail.
I want to be able to add a variable to a string and then work with it as a variable name, not a string.
The scenario is that I’m looking up something in an ADO Recordset (rstRecordset!FieldName) where field is the following “Y” & Year(Now) – 1950. A new field is added each year corresponding to the amount of years since 1950, and I need to be able to reference these fields in the future without having to change the code each time. Of course I could do something like put in references for the next 20 years or so and then determine whether that field exists before summing, or something of the sort, but the main question that I’m trying to get answered is conceptually how can I go from the string value (this year it would be “rstRecordset!Y51”) and get the underlying data represented.
Thanks for your time.

Mike

Try:

Dim nYear as Integer

for nYear = 1 to Datediff(y,"1950-01-01",Now)
Debug.print rstRecordset.Fields("Y" & nYear).Value
next nYear




HTH,
D.

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

Another way to do this (not by code) is to use the table's Metadata, checking that the field name starts with Y, then getting the corresponding year for the field. You can iterate the columns collection of the table schema.

Let me know if I understood your issue.