Query to look up value from other field

[Diggsy]

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

 

[Duane Hookom]

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.