Expression exceeds 1,024-character limit

[bsarman]

My query doens't work after the 13th IIf statement and gives me this message:
"The expression you entered exceeds the 1,024-character limit for the query design grid".
How can I resolve this issue?
Thanks,

SELECT IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*MV*","MV 30 Days Prior To Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RH*","RH 30 Days Prior To Expire",

IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RE*","RE At Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*CE*","CE At Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*AG*","AG At Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*AC*","AC At Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RX*","RX At Expire",

IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*RE*","RE 30 Days After Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*CE*","CE 30 Days After Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*AG*","AG 30 Days After Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*AC*","AC 30 Days After Expire",
IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*RX*","RX 30 Days After Expire",

IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*MV*","MV 30 Days After Expire",


""))))))))))))) AS ["KeyCodeList"], qryJOE_REPORT_2.Subscription_Def, qryJOE_REPORT_2.MaxOfEffortLevel, qryJOE_REPORT_2.Customer_ID, qryJOE_REPORT_2.CallResult, qryJOE_REPORT_2.RepName
FROM qryJOE_REPORT_2;

 

[flyover789]

the first attempt that comes to my mind is to shorten the fieldnames:

i.s.o. "qryJOE_REPORT_2" you could use "qryJR2"
i.s.o. "subscription_def" "s_def"
a.s.o.

you'd save a lot of characters in your SQL

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[scriverb]

Secondly, implement the use of Alias designations for the tables. Use a single letter and you elminate a whole bunch of characters in your query:

SELECT IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*MV*","MV 30 Days Prior To Expire",
IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*RH*","RH 30 Days Prior To Expire",

IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*RE*","RE At Expire",
IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*CE*","CE At Expire",
IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*AG*","AG At Expire",
IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*AC*","AC At Expire",
IIf([JR2.MaxOfEffortLevel]="4" And [JR2.subscription_def] Like "*RX*","RX At Expire",

IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*RE*","RE 30 Days After Expire",
IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*CE*","CE 30 Days After Expire",
IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*AG*","AG 30 Days After Expire",
IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*AC*","AC 30 Days After Expire",
IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*RX*","RX 30 Days After Expire",

IIf([JR2.MaxOfEffortLevel]="5" And [JR2.subscription_def] Like "*MV*","MV 30 Days After Expire",


""))))))))))))) AS ["KeyCodeList"], JR2.Subscription_Def,  JR2.MaxOfEffortLevel, JR2.Customer_ID, JR2.CallResult, JR2.RepName
FROM qryJOE_REPORT_2 [red]as JR2[/red];

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

You may also consider writing a public function to do the conditionals instead of an unreadable huge Iif imbroglio.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

PHV: Absolutely. I have even trying to read those nexted((nexted)nested)nested) things. A function definately would be in order.

I see from your profile that you are in France. Just got off the phone with my son who is in Paris for 2 months. He has married a French girl and lives in Oakland, CA.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[bsarman]

Thank you all. I'm making all the names shorter and will see if that helps.

Thanks again.

 

[JonFer]

This should be more efficient for the KeyCodeList expression (there may be typos or mismatched parens):

IIf([JR2.MaxOfEffortLevel] In ("4","5"),
IIf([JR2.subscription_def] Like "*MV*","MV",
IIf([JR2.subscription_def] Like "*RH*","RH",
IIf([JR2.subscription_def] Like "*RE*","RE",
IIf([JR2.subscription_def] Like "*CE*","CE",
IIf([JR2.subscription_def] Like "*AG*","AG",
IIf([JR2.subscription_def] Like "*AC*","AC",
IIf([JR2.subscription_def] Like "*RX*","RX",""))))),"") &

IIf([JR2.MaxOfEffortLevel]="4",
IIf([JR2.subscription_def] Like "*MV*" or
[JR2.subscription_def] Like "*RH"," Prior To Expire",
" At Expire"),
IIf([JR2.MaxOfEffortLevel]="5"," 30 Days After Expire",""))

It will include records with subscription_def like "*RH*" and MaxOfEffortLevel="5" which your original expression did not. You can add one more IIf to take care of that if needed.

 

[bsarman]

I didn't realize access had a 1024 character limit. I broke up the query and created a union query afterwards to put the results together.

It's kind of a long way of doing it. But it worked.

Thanks for the posts.