Query Too Complex - How call Function?

[cariengon]

I'm trying to create a query that contains several Iif statements. I have a total of 14 within the query. When I add one more - it gives me the "Too Complex" error.

I only need to add one more and I have no clue as how to call a function from a query.

Can anyone help? Here's my sql:

Premium Type: IIf([Prem_Type] Like "ADDASO*","ASO Premium",IIf([Prem_Type]
Like "AOLIFEASO*","ASO Premium",IIf([Prem_Type] Like "LIFEASO*","ASO
Premium",IIf([Prem_Type] Like "LTDASO*","ASO Premium",IIf([Prem_Type] Like
"OPTLIFEASO*","ASO Premium",IIf([Prem_Type] Like "SUPPADDASO*","ASO
Premium",IIf([Prem_Type] Like "SUPPLIFEASO*","ASO Premium",IIf([Prem_Type]
Like "VOLADDASO*","ASO Premium",IIf([Prem_Type] Like "DENTALASO*","Stop Loss
Premium",IIf([Prem_Type] Like "FBAASO*","Stop Loss Premium",IIf([Prem_Type]
Like "MEDICALASO*","Stop Loss Premium",IIf([Prem_Type] Like "PCSASO*","Stop
Loss Premium",IIf([Prem_Type] Like "STDASO*","Stop Loss
Premium",IIf([Prem_Type] Like "VISIONASO*","Stop Loss Premium","Fully
Insured"))))))))))))))

I want to add -
IIf([Prem_Type] Like "LUMP SUM*","Acct Adj",

 

[Pat Hartman]

There are at least two better ways to do this.

1. Create a lookup table with two fields - the code and the description. Then join to this table on the code field and select the description for display.

2. Create a public function that contains a case statement to translate the code to text. To use it in your query replace all the nested IIf()'s with:

PremiumType:YourFunctionName(Prem_Type)