[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Expression cannot be longer than 2048 characters

I'm running into the above noted error on the statement below.  I probably need to assign the value of:

forms!frmBulkListGenerator.BulkList.Column

to a shorter variable name, but I don't know how to do that.  I would appreciate some guidance to get me through this.  Thanks.  Here is the problem line:

=IIf(Forms!frmmain!Corresponder=1,forms!frmBulkListGenerator.BulkList.Column(1, 9) & Chr(13) & Chr(10) & IIf(IsNull(forms!frmBulkListGenerator.BulkList.Column(1, 9)), '', 'Attention:  ') & forms!frmBulkListGenerator.BulkList.Column(1, 10) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 11) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 12) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 28) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 29),IIf(Forms!frmmain!Corresponder=2,forms!frmBulkListGenerator.BulkList.Column(1, 5) & ' Tax Department' & Chr(13) & Chr(10) & 'Attention:  ' & IIf(forms!frmBulkListGenerator.BulkList.Column(1, 38)<>'' Or forms!frmBulkListGenerator.BulkList.Column(1, 39)<>'',forms!frmBulkListGenerator.BulkList.Column(1, 37) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 38) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 39), 'Customer Service Department') & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 40
) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 41),IIf(Forms!frmmain!Corresponder=3, 'MPAC Region No. ' & forms!frmBulkListGenerator.BulkList.Column(1, 43) & Chr(13) & Chr(10) & 'Attention:  ' & IIf(forms!frmBulkListGenerator.BulkList.Column(1, 48)<>'' Or forms!frmBulkListGenerator.BulkList.Column(1, 49)<>'', forms!frmBulkListGenerator.BulkList.Column(1, 47) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 48) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 49), 'Customer Service Department') & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 44) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 45), IIf(Forms!frmmain!Corresponder=5, 'Assessment Review Board' & Chr(13) & Chr(10) & 'Attention:  ' & Forms!frmMain!OtherTitle & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 53) & ' / Region ' & forms!frmBulkListGenerator.BulkList.Column(1, 43) & ' Caseworker' & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 57) & Chr(13)
 & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 58),Forms!frmMain!OtherCompanyName & Chr(13) & Chr(10) & 'Attention:  ' & Forms!frmMain!OtherTitle & ' ' & Forms!frmMain!OtherFirstName & ' ' & Forms!frmMain!OtherLastName & Chr(13) & Chr(10) & Forms!frmMain!OtherAddress1 & Chr(13) & Chr(10) & Forms!frmMain!OtherAddress2))))
0
DanielAttard
Asked:
DanielAttard
3 Solutions
 
DanielAttardAuthor Commented:
Darn - no one up late tonight to be able to help me.  I guess I'll have to grab some shut-eye and check back in the morning!
0
 
HilaireCommented:
>>Darn - no one up late tonight to be able to help me<<
Most likely because you didn't post in the most relevant area. This is the SQL Server topic area.

As for your problem above, I'm not sure whether the error is due to a too long line
or a too long expression.
The code looks like VBA/VB
I'd suggest :
- using the "_" to continue a statement on the following line
ie
"this line is too " & "long"
could write
"this line is too " & _
      "long"
this might help you make the code more readable and make sure you have a proper indentation

- replacing " & Chr(13) & Chr(10) & " with the shorter " & vbCrLf & " , which is exactly the same IMHO

HTH
Hilaire
0
 
lluthienCommented:
although hillaire 's  solution might help in this case,

i'm afraid your battling symptoms with that.
the problem is more along the lines of..
"why do you have a 2K+ character expression?"

if this is really getting you the result you want,
i suggest splitting it up in logical sections.
that might also help in case you have to ever debug this.. ehm ..
line.

:)

cheers
0
 
puranik_pCommented:
Even use of _ to join lines has got a max limit.

You can split the string in part..like...

MyString = IIf(Forms!frmmain!Corresponder=1,forms!frmBulkListGenerator.BulkList.Column(1, 9) & Chr(13) & Chr(10) & IIf(IsNull(forms!frmBulkListGenerator.BulkList.Column(1, 9)), '', 'Attention:  ') & forms!frmBulkListGenerator.BulkList.Column(1, 10) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 11) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 12) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 28) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 29),IIf(Forms!frmmain!Corresponder=2,forms!frmBulkListGenerator.BulkList.Column(1, 5) & ' Tax Department' & Chr(13) & Chr(10) & 'Attention:  ' & IIf(forms!frmBulkListGenerator.BulkList.Column(1, 38)<>'' Or forms!frmBulkListGenerator.BulkList.Column(1, 39)<>'',forms!frmBulkListGenerator.BulkList.Column(1, 37) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 38) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 39), 'Customer Service Department') & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 40
)

MyString = MyString & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 41),IIf(Forms!frmmain!Corresponder=3, 'MPAC Region No. ' & forms!frmBulkListGenerator.BulkList.Column(1, 43) & Chr(13) & Chr(10) & 'Attention:  ' & IIf(forms!frmBulkListGenerator.BulkList.Column(1, 48)<>'' Or forms!frmBulkListGenerator.BulkList.Column(1, 49)<>'', forms!frmBulkListGenerator.BulkList.Column(1, 47) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 48) & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 49), 'Customer Service Department') & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 44) & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 45), IIf(Forms!frmmain!Corresponder=5, 'Assessment Review Board' & Chr(13) & Chr(10) & 'Attention:  ' & Forms!frmMain!OtherTitle & ' ' & forms!frmBulkListGenerator.BulkList.Column(1, 53) & ' / Region ' & forms!frmBulkListGenerator.BulkList.Column(1, 43) & ' Caseworker' & Chr(13) & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 57) & Chr(13)
 & Chr(10) & forms!frmBulkListGenerator.BulkList.Column(1, 58),Forms!frmMain!OtherCompanyName & Chr(13) & Chr(10) & 'Attention:  ' & Forms!frmMain!OtherTitle & ' ' & Forms!frmMain!OtherFirstName & ' ' & Forms!frmMain!OtherLastName & Chr(13) & Chr(10) & Forms!frmMain!OtherAddress1 & Chr(13) & Chr(10) & Forms!frmMain!OtherAddress2))))

I've splitted it into two. Maybe you want to split it more number of times.

Pura
0
 
DanielAttardAuthor Commented:
My apologies for posting this question in the wrong topic area.  I solved the problem through a combination of approaches, so I am splitting the points.  Thanks to all.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now