I need a criteria expresion which will allow the selection of any one of five fields in one table depending on the value of a criteria field in a second table.
For instance, the fields are field1, field2, field3, field4 and field5. Depending on the criteria field, ([Area]), from a second table, I want to insert one of the 5 fields. I tried a nested iif function statement, but access keeps giving me an error. I can use the iif function to find one of two fields but it will not allow me to nest them.
I tried:
iif ([tbl 2]![area]=washer,[tbl 1]![Field1],iif ([tbl 2]![Area]='Plant",[tbl 1]![Field2],.......,[Field5])))))
Any suggestions?
For instance, the fields are field1, field2, field3, field4 and field5. Depending on the criteria field, ([Area]), from a second table, I want to insert one of the 5 fields. I tried a nested iif function statement, but access keeps giving me an error. I can use the iif function to find one of two fields but it will not allow me to nest them.
I tried:
iif ([tbl 2]![area]=washer,[tbl 1]![Field1],iif ([tbl 2]![Area]='Plant",[tbl 1]![Field2],.......,[Field5])))))
Any suggestions?
