Access says I have +2,048 chars when I don't.

[nkrst1]

I'm simply adding a little bit of code to a control source in a report, but once I'm done, it keeps saying that I can't have an expression longer than 2,048 characters in control source, which it isn't. You don't have to read this, but if you do a cut and paste, i'm pretty sure that you'll find that the character count of this is less than 1,500 chars. It's driving me nuts. All I want it do is have the first name of a nun follow "Sister" or "Sr." rather than the last.

=Trim("Dear " & (IIf([Enter Contact Type, if any, to address] Is Null,[sName],(IIf([Enter Contact Type, if any, to address]="Principal",(IIf([TypeName] Is Not Null,(IIf([DearLine] Is Not Null,[DearLine],[Salutation] & " " & IIf(InStr([Salutation], "sister&quot or InStr([Salutation],"sr&quot,[FirstName],[LastName]))),"Principal&quot),(IIf([Enter Contact Type, if any, to address]="financial administrator",(IIf([TypeName] Is Not Null,(IIf([DearLine] Is Not Null,[DearLine],[Salutation] & " " & IIf(InStr([Salutation], "sister&quot or InStr([Salutation],"sr&quot,[FirstName],[LastName]))),"Financial Administrator&quot),(IIf([Enter Contact Type, if any, to address]="admissions director",(IIf([TypeName] Is Not Null,(IIf([DearLine] Is Not Null,[DearLine],[Salutation] & " " & IIf(InStr([Salutation], "sister&quot or InStr([Salutation],"sr&quot,[FirstName],[LastName]))),"Admissions Director&quot),(IIf([Enter Contact Type, if any, to address]="main contact" Or [Enter Contact Type, if any, to address]="checks Addressee",(IIf([TypeName] Is Not Null,(IIf([DearLine] Is Not Null,[DearLine],[Salutation] & " " & IIf(InStr([Salutation], "sister&quot or InStr([Salutation],"sr&quot,[FirstName],[LastName]))),"Principal&quot))))))))))) & ":&quot

Any help would be much appreciated.

-n-

 

[flaniganmj]

n,

I not for sure on this but I belive in a control source you can only nest up to 10 IIf statements within another IIf statement. It looks like you have many more than that. I think you will need to do your If..Then logic in code or utilize external subqueries. Anyone else have an idea?

flan

 

[scriverb]

If flanpk's observation is correct look into using the Switch function statement in conjunction with IIF's. There are an infinite number of expression evaluations in a Switch statement according to ACCESS Help. Bob Scriver

 

[threadgills]

I have a similar problem, only I am using the CHOOSE function. This formula is already in an unbound field and all I am trying to do is CHANGE the numbers:

=[txtNewPremAccts]/(Choose([txtCurMth],([New_Prem_Accts_10]+[New_Prem_Accts_11]+[New_Prem_Accts_11]),([New_Prem_Accts_11]+[New_Prem_Accts_11]+[New_Prem_Accts_1]),([New_Prem_Accts_1]+[New_Prem_Accts_1]+[New_Prem_Accts_2]),([New_Prem_Accts_1]+[New_Prem_Accts_2]+[New_Prem_Accts_3]),([New_Prem_Accts_2]+[New_Prem_Accts_3]+[New_Prem_Accts_4]),([New_Prem_Accts_3]+[New_Prem_Accts_4]+[New_Prem_Accts_5]),([New_Prem_Accts_4]+[New_Prem_Accts_5]+[New_Prem_Accts_6]),([New_Prem_Accts_5]+[New_Prem_Accts_6]+[New_Prem_Accts_7]),([New_Prem_Accts_6]+[New_Prem_Accts_7]+[New_Prem_Accts_8]),([New_Prem_Accts_7]+[New_Prem_Accts_8]+[New_Prem_Accts_9]),([New_Prem_Accts_8]+[New_Prem_Accts_9]+[New_Prem_Accts_10]),([New_Prem_Accts_9]+[New_Prem_Accts_10]+[New_Prem_Accts_11]))/3+(Choose([txtCurMth],([Prop_New_prem_Invt_accts_10]+[Prop_New_prem_Invt_accts_11]+[Prop_New_prem_Invt_accts_11]),([Prop_New_prem_Invt_accts_11]+[Prop_New_prem_Invt_accts_11]+[Prop_New_prem_Invt_accts_1]),([Prop_New_prem_Invt_accts_1]+[Prop_New_prem_Invt_accts_1]+[Prop_New_prem_Invt_accts_2]),([Prop_New_prem_Invt_accts_1]+[Prop_New_prem_Invt_accts_2]+[Prop_New_prem_Invt_accts_3]),([Prop_New_prem_Invt_accts_2]+[Prop_New_prem_Invt_accts_3]+[Prop_New_prem_Invt_accts_4]),([Prop_New_prem_Invt_accts_3]+[Prop_New_prem_Invt_accts_4]+[Prop_New_prem_Invt_accts_5]),([Prop_New_prem_Invt_accts_4]+[Prop_New_prem_Invt_accts_5]+[Prop_New_prem_Invt_accts_6]),([Prop_New_prem_Invt_accts_5]+[Prop_New_prem_Invt_accts_6]+[Prop_New_prem_Invt_accts_7]),([Prop_New_prem_Invt_accts_6]+[Prop_New_prem_Invt_accts_7]+[Prop_New_prem_Invt_accts_8]),([Prop_New_prem_Invt_accts_7]+[Prop_New_prem_Invt_accts_8]+[Prop_New_prem_Invt_accts_9]),([Prop_New_prem_Invt_accts_8]+[Prop_New_prem_Invt_accts_9]+[Prop_New_prem_Invt_accts_10]),([Prop_New_prem_Invt_accts_9]+[Prop_New_prem_Invt_accts_10]+[Prop_New_prem_Invt_accts_11]))/3))

When I try to change numbers, it tells me the expression can't be longer than 2048 characters when I only have 1858 and not adding, but changing!!

Any ideas?

 

[scriverb]

It may sound strange but try cuting the formula to a word processor and make the changes. Then copy and paste it back into the control source. This has worked for me when I have received this problem. Seems like ACCESS has to reset the character count completely to get it to work correctly when you are getting close to the total allowable. By clearing the expression and then pasting in a new one it seems to work.

Let me know if this works for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[threadgills]

That's what I've had to do on quite a few.

I tried splitting this one up between the two CHOOSE functions and pasting into two separate unbound fields. The first CHOOSE worked and the second didn't. Same error. Also tried pasting into a new...it was a no go also. Access is a good tool for small time, but it's limitations are crazy.

Thanks for the input Bob!!!

 

[MichaelRed]

In both of these, I would think the sheer length of the statement would convince most to generate an alternative. For Me (being an old-time PROGRAMMER), that would be to construct the value in a module, so as to be able to deal with the sub parts individually.

nkrst1 appears to be just attempting to construct a letter heading, as in Dear [Sir | madam | Principal | financial administrator | admissions director | etc], so I would think the various forms of address could be reasonably deduced via a Select Case block.

Similarly, threadgills's schema resembles a simple quarterly summation (although there ARE some 'oddities / exception), so again, a simple select case statement (in a procedure) could easily replace the tiresome and repitious typeing and chacking for balanced syntax.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places