IIF Statement in Access - Invalid String error message

Question

Hello - Can anyone help on why I'm getting this error message?

=IIf([dbo.PoPurchGroup].[code]="COSPAC", "Central Oil", IIf([dbo.PoPurchGroup].[code]="RELADYNE", "Central Oil", IIf([dbo.PoPurchGroup].[code]="COSBR", "Central Oil", IIf([dbo.PoPurchGroup].[code]="Robert Only", "Shell Bulk", IIf([dbo.PoPurchGroup].[code]="SHEBUL", "Shell Bulk", IIf([dbo.PoPurchGroup].[code]="SHEBUL-MS", "Shell Bulk-MS", IIf([dbo.PoPurchGroup].[code]="SHEPAC", "Shell Package", "OTHER”)))))))

I even tried just 1 statement and I get a " is invalid error.

I've followed the example:

=IIf([CountryRegion]="Italy", "Italian", IIf([CountryRegion]="France", "French", IIf([CountryRegion]="Germany", "German", "Some other language")))

Answer 1

I strongly endorse what Peter and Duane have said.  You are currently encoding data in an expression.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

By storing the data correctly in a table you simply need to join that table to your current table on the Code columns to return the appropriate value.

Answer 2

I totally agree with Peter. Consider reading this blog post https://weblogs.sqlteam.com/jeffs/2006/02/10/9002/.

Answer 3

Put the data in a table (code, full name). In your solution you need to rebuild your application If a new value needs to be added.

Answer 4

Are you sure you got the opening anc closing parentheses correct?

You might try building the expression one nested IIF at a time.

Or you might try using the SWITCH function: Switch Function (microsoft.com)