Build field name in an expression, then call the field?

[DB88]

Hello,



I have table, called Inflation, that has a record for every "base year". Within each record is a column that contains the inflation rate index for every year between 1970 and 2030 (field names are Inflation.Y1970 through Inflation.Y2030) as a function of that base year. This table is linked to TableA using base year as the key. Several values need to be adjusted for inflation. The problem is that many are in different "then year" values (but all have the same base year). So I need to get different inflation rates depending on the Then Year.

Suppose that the field, TableA.ThenYear is 2013. I built an expression, RateField, in my query that returns "[Inflation]![Y2013]". The expression RateField returns the name of the field that I need to pull data from. Is there a calculation function that will convert the text "[Inflation]![Y2013]" into a field? Aka how would I build the expression, TYrate, such that it used RateField to return the 2014 inflation rate and not the text sting?

I've used this sort of trick a lot in other software and am hopeing it is possible in Access.

THanks

[lfpm062010]

I am not sure I understand you fully.

Table "Inflation" has column "Year" and "Rate". So, it would look something like this.
YEAR RATE
==== ====
1970 0.1
1971 0.15
.
.
.
2014 0.12
.
.
.

If your "TableA" has a "YEAR" field. I would just join the two table with the "YEAR" field and use "RATE" to do your calculation.

SELECT Inflation.YEAR, Inflation.RATE
FROM Inflation INNER JOIN TableA ON Inflation.YEAR = TableA.YEAR;

[DB88]

No, I my data has a column for base year and a column for the inflation rate for each year:

BaseYear Y1970 Y1971 Y1972 ....... Y2030
====== ==== ==== === ====
2006 0.5 0.52 0.54 2.5
2007
2008

Because the inflation rates change depending on the base year. So base year is the key that connects to TableA
My database will create cost models for programs that are 15 -30 years. Beacuse these programs have such long life cycles various cost data is in different "Then Years". The model needs to be able to covert all cost data back to base year. I need to write a query that will grab the correct inflation index for a given piece of data in Table A.

Here is a simple example: In TableA there will be the following fields; BaseYear=2006, AnnualCost1=$11,000, AnnualCost1_ThenYear=2010. In the query I join Inflation and TableA using BaseYear. I can create the field RateField = "[Inflation]![Y2010]". I would like to use RateField (which is a text field containing me desired field name) to grab the value in column Y2010 in the Inflation table. Aka I want to write a calculation, TYrate, whose input is RateField and whose output is the actual value stored in field Inflation.Y2010.

Hope that makes more sense

[orange]

Not exactly. Are you hoping to get a new table and value that table?

In your simple example, could you tell us WHAT you want in plain English, NOT HOW you are doing something that isn't working?

I'm not following what thenyear 2010 represents.

You could build a new table with field names, datatypes you want. Then, using a logic loop, populates the table values, but I'm not following.

[DB88]

I want a function that gets the value of field given the field name as a text string.

So, if the value of field, F1, is 10. I want a function such that function("F1") = 10, where "F1" is a text string

If you are familar with FileMaker, I want the function GetField

Is that a better explanation?


(I need to create an expression but I don't want to hard code which field is used in the expression. Depending on the situation, the expression will need to pull from 1 of 30 different fields.)

[orange]

I don't know Filemaker.

Why can't you use a query

Select fieldName from Table?

[DB88]

I am using a query!

Since no one seems to know what I'm talking about, I have my answer. Access does not have the capability. I have to hard code a field name into an expression.

[lfpm062010]

You need to use VBA to get the value from your table design.
BaseYear Y1970 Y1971 Y1972 ....... Y2030
====== ==== ==== === ====
2006 0.5 0.52 0.54 2.5
2007
2008
Sub Test()
Dim BaseYR as string
Dim LOOKUPYR as String
Dim tmpStr as String
Dim tmpValue As Double
BaseYR = "2013"
LOOKUPYR = "Y2014"
tmpStr = "TABLENAME.BaseYear = """ & BaseYR & """ "
tmpValue = DLookup(LOOKUPYR, "TABLENAME", tmpStr)
End Sub