Query to look up value from other field

Hello,

I am currently working on a query that looks up a specific field based on my
hardcoded input. I have 52 fields in a table named Forecasts The fields are
Named Week 01 Forecast to Week 52 Forecast. If I hard code a specific value
as in my example... it will retrieve the data in Field Week 08 Forecast. I am
try to find a way to automatically update the name by changing the middle
part ..08 ...to a value from a field called WK which contains a 2 digit
number based on other paremeters in my query. For instance if the data in WK
Field was 03. Is there a way I can call that in my query instead of having to
hardcode it every time?



Currently
Forecast: [Forecasts]![Week 08 Forecast]
would like [Forecasts]![Week [WK] Forecast] to give me [Forecasts]![Week 03
Forecast]


I know it can be done I think I am having syntax errors. Any sggestions
would be helpful.

Thanks

Chris

You can't parameterize the field name.

You could move your spreadsheet to Excel or possibly normalize it in Access.
The week number should be a value stored in a field named "WeekNum" or
similar. Each forecast value should create its own record.

--
Duane Hookom
Microsoft Access MVP


"Diggsy" wrote:

Hello,

I am currently working on a query that looks up a specific field based on my
hardcoded input. I have 52 fields in a table named Forecasts The fields are
Named Week 01 Forecast to Week 52 Forecast. If I hard code a specific value
as in my example... it will retrieve the data in Field Week 08 Forecast. I am
try to find a way to automatically update the name by changing the middle
part ..08 ...to a value from a field called WK which contains a 2 digit
number based on other paremeters in my query. For instance if the data in WK
Field was 03. Is there a way I can call that in my query instead of having to
hardcode it every time?



Currently
Forecast: [Forecasts]![Week 08 Forecast]
would like [Forecasts]![Week [WK] Forecast] to give me [Forecasts]![Week 03
Forecast]


I know it can be done I think I am having syntax errors. Any sggestions
would be helpful.

Thanks

Chris