Neste IIf Statement in Expression builder

[Guest]

Hello,

I am using a nested IIF in Expression builder. The purpose of the expression
was to take the value in WKS and if it matched it would return a field from a
table called Forecast. It started to work but it eventually gave me an error
because my expressin was too complex. i wanted to put 52 conditions and it
errored before I got 20. Here is what I have so far


Expr2: IIf([WKS]=11,[Forecasts]![Week 11 Forecast],
IIf([WKS]=12,[Forecasts]![Week 12 Forecast],IIf([WKS]=13,[Forecasts]![Week
13 Forecast],IIf([WKS]=14,[Forecasts]![Week 14
Forecast],IIf([WKS]=15,[Forecasts]![Week 15
Forecast],IIf([WKS]=16,[Forecasts]![Week 16
Forecast],IIf([WKS]=17,[Forecasts]![Week 17
Forecast],IIf([WKS]=18,[Forecasts]![Week 18
Forecast],IIf([WKS]=19,[Forecasts]![Week 19
Forecast],IIf([WKS]=20,[Forecasts]![Week 20
Forecast],IIf([WKS]=21,[Forecasts]![Week 21
Forecast],IIf([WKS]=22,[Forecasts]![Week 22
Forecast],IIf([WKS]=23,[Forecasts]![Week 23
Forecast],IIf([WKS]=24,[Forecasts]![Week 24
Forecast],IIf([WKS]=25,[Forecasts]![Week 25
Forecast],IIf([WKS]=26,[Forecasts]![Week 26
Forecast],IIf([WKS]=27,[Forecasts]![Week 27
Forecast],IIf([WKS]=28,[Forecasts]![Week 28
Forecast],IIf([WKS]=29,[Forecasts]![Week 29
Forecast],IIf([WKS]=30,[Forecasts]![Week 30 Forecast],""))))))))))))))))))))

My question is is there a way of doing this and what would the code look like.

Thanks

Chris

 

[Douglas J. Steele]

I hate to break this to you, but your table is definitely not properly
designed if you have 52 fields named [Week n Forecast] in it.

You should have 52 rows in the table, one for each week, not 52 fields in a
single row in the table.

You might find it useful to check out some of the references Jeff Conrad has
at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101