Putting data together in text box

[keiem]

In a report and in an unbound form text box, I would like to display a narrative description based upon data (text) from various fields in an underlying table. I know how to do this in a form and report using the IIf() function and the "&" operator. My problem is that after accomodating the various conditions that exist in my data to create a properly worded description, my code is greater than the some 2,288 characters allowed in an expression. Can anyone think of another way to do this?

Here is an example of my code:

="The property contains a " & (IIf(IsNull([STORIES]),"",(IIf([STORIES]=1,"one",(IIf([STORIES]=2,"two",(IIf([STORIES]=3,"three",(IIf([STORIES]=4,"four","one&quot))))))))) & (IIf(IsNull([HALFSTORY]),"-story "," and a half-story &quot) & (IIf(IsNull([FUNCT_CUR]),"structure",(IIf([FUNCT_CUR]="Other","structure",(IIf([FUNCT_CUR]="Social" Or [FUNCT_CUR]="Government" Or [FUNCT_CUR]="Education" Or [FUNCT_CUR]="Recreation" Or [FUNCT_CUR]="Transportation"," a " & LCase([FUNCT_CUR]) & " building",(IIf([FUNCT_CUR]="Landscape"," a " & LCase([FUNCT_CUR]) & " feature",LCase([FUNCT_CUR])))))))) & (IIf(IsNull([PLAN]) And IsNull([STYLE1]) And IsNull([STYLE2]) And IsNull([STYLE3]),". "," with&quot))

Also, once it is generated, is there a way to save the resulting text to a separate field in the table?

Thanks!

 

[scriverb]

Since your expression is obviously quite complex and the limitations of the expression size is not negotiable I would build the text string in an event procedure in the form and the report and then make an assignment of the result to an object in form/report.

Form: In the procedure where you select the record perform the building of your expression in code and then assign the value to the object on the form. This way you can break up the IIF somewhat to make it a little more manageable and understandable.

Example:
Dim vTextString as string
vTextString = ="The property contains a " & (IIf(IsNull([STORIES]),"",(IIf([STORIES]=1,"one",(IIf([STORIES]=2,"two",(IIf([STORIES]=3,"three",(IIf([STORIES]=4,"four","one&quot)))))))))

vTextString = vTextString & (IIf(IsNull([HALFSTORY]),"-story "," and a half-story &quot) & (IIf(IsNull([FUNCT_CUR]),"structure",(IIf([FUNCT_CUR]="Other","structure",(IIf([FUNCT_CUR]="Social" Or [FUNCT_CUR]="Government" Or [FUNCT_CUR]="Education" Or [FUNCT_CUR]="Recreation" Or [FUNCT_CUR]="Transportation"," a "

vTextString = vTextString & LCase([FUNCT_CUR]) & " building",(IIf([FUNCT_CUR]="Landscape"," a " & LCase([FUNCT_CUR]) & " feature",LCase([FUNCT_CUR])))))))) & (IIf(IsNull([PLAN]) And IsNull([STYLE1]) And IsNull([STYLE2]) And IsNull([STYLE3]),". "," with&quot))

me![FormObject] = vTextString

Report:
You could also perform the same type of thing in the Detail Sections OnFormat Event Procedure. Then make a similar assignment to the object in the Detail Section of the report.

This way you are not limited to the size limitations of a single expression. It is also much easier to read and debug if you have problems with the result.