issue with to many IIF statements in my DoCmd.RunSQL command

[cirugio]

Within my ACCESS database, I have some sql logic built within a function which utilizes the IIF statement. It was working fine until I had to expand it. Now I am receiving a Run-time error '3075': Expression too complex in query expression 'IIF(............. I am assuming there is to many IIF statements. Is there a way I can rewrite this? Especially, since this will continue to expand over time.

I would love to use case statements but I don't think it is allowed within access. Any help is greatly appreciated. Thanks for your time in advance.



Code I currently have in place:

DoCmd.RunSQL "UPDATE ALL_Midas_Data_FINAL as F " & _
"SET F.w_bank_name = IIf(F.bank = '0021','Italy', " & _
"IIf(F.bank = '0022','Ireland', " & _
"IIf(F.bank = '0154','Saudi', " & _
"IIf(F.bank = '0377','Germany', " & _
"IIf(F.bank = '0069','Paris', " & _
"IIf(F.bank = '0062','Chicago', " & _
"IIf(F.bank = '0349','Florida', " & _
"IIf(F.bank = '0088','Canada', " & _
"IIf(F.bank = '0089','India', " & _
"IIf(F.bank = '0028','Beijing', " & _
"IIf(F.bank = '0009','London', " & _
"IIf(F.bank = '0075','NTGS', " & _
"IIf(F.bank = '050x','Singapore', " & _
"IIf(F.bank = '050u','Singapore', " & _
"IIf(F.bank = '0079','Luxembourg', " & _
"IIF(F.bank = '0073','Scotland', " & _
"IIf(F.bank = '0023','Australia','??????')))))))))))))))));"

 

[Joe4]

It looks like what you have is a straight lookup situation, so just create a lookup table, and link that table to it on your "bank" field.
Then all you have to do to expand is add more records to your lookup table (shouldn't have to change your code at all).

 

[cirugio]

Thank you! not sure why I did not think of that. I guess, I was just thinking about expanding what was already there. Your approach is much cleaner. Thanks again!!!!