Query Error? - "The text is to long to be edited"

[robertr]

Thanks to this amazing forum, I have created some queries that have resolved a lot of my query issues. However I created a long query that I placed in the "FIELD" section of the query and am receiving an error "The text is to long to be edited". What can I do to include this monster iif statement to my field in my query. I need this as one column in my query and can't split it up. Help!!!!

=iif([Option]="SUPLIFE1X" and [Plan]="SUPLIFE","1 x Annual Base Salary",
iif([Option]="SUPLIFE2X" and [Plan]="SUPLIFE","2 x Annual Base Salary",
iif([Option]="SUPLIFE3X" and [Plan]="SUPLIFE","3 x Annual Base Salary",
iif([Option]="WAIVE" and [Plan]="SUPLIFE","Waive Coverage",
iif([Option]="CHL10K" and [Plan]="SUPLIFE","CHLIFE",
iif([Option]="WAIVE" and [Plan]="CHLIFE","Waive",
iif([Option]="SPL10K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL15K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL20K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL25K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL30K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL35K" and [Plan]="SPLIFE","Enroll",


iif([Option]="SPL40K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL45K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL50K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL55K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL60K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL65K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL70K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL75K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL80K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL85K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL90K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL95K" and [Plan]="SPLIFE","Enroll",
iif([Option]="SPL100K" and [Plan]="SPLIFE","Enroll",
iif([Option]="WAIVE" and [Plan]="SPLIFE","Waive"))))))))))))))))))))))))))

[ajetrumpet]

first, you look up the error to see what it means. second, you put the common areas into on section of one statement:

iif(plan="splife", 
iif(clng(mid(option, 4, len(option) - 2))) >= 10,
iif(clng(mid(option, 4, len(option) - 2))) <= 100, "SPLIFE", ""),
iif(option = "waive", "Waive", ""),
iif(plan, "suplife", 
ETC....

third, realize that doing this in vb with case statements might be easier anyway.

[robertr]

I am not an expert in VB. Where do I start?

[ajetrumpet]

I am not an expert in VB. Where do I start?

how about starting by trying to implement the consolidated IIF() statement version that I posted?