Text box not showing appopriate information

[Guest]

I am designing a report for our customers to show upcoming changes to their
product and their options with regard to the change. Not all customers will
have the same number of options. I have a table with the customer
information and options (which includes their rates and descriptions) which
feeds into a report. I am trying to present some of this infomation in a
text box with the Can Grow Option set to yes and with the Control Source
property set to: =Trim([BCPOpt2Descr]) & IIf([BCPOpt3Rate]<>0,"
" & Trim([BCPOpt3Descr]),"") & IIf([BCPOpt4Rate]<>0,"
" & Trim([BCPOpt4Descr]),"") & IIf([BCPOpt5Rate]<>0,"
" & Trim([BCPOpt5Descr]),"") & IIf([BCPOpt6Rate]<>0,"
" & Trim([BCPOpt6Descr]),"") & IIf([BCPOpt7Rate]<>0,"
" & Trim([BCPOpt7Descr]),"") & IIf([BCPOpt8Rate]<>0,"
" & Trim([BCPOpt8Descr]),"")
Carriage returns are the first value of the strings in the IIf statements
when the statement is true to provide a single line for each option. There
is always a second option, but there may not be options 3 - 8, with the lower
numbered options being filled first. In the case of just two options (option
1 is shown elsewhere on the report) the text box is displayed correctly. If
there are more than two options, the last option is not shown even though
there is a non-zero rate. This happens no matter which option is the last.
If I make the text box big enough to fill the max 7 lines, it will show the
final line appropriately, but leaves unwanted white space after the last
option in the cases where there are less than 8 options. Setting the text box
Can Shrink property to yes will then remove the white space, but will remove
the last option again. Can anyone help me get the text box to display the
correct infomation? Thank you.

 

[Larry Linson]

Have you considered stacking text boxes for each value, and setting the
CanShrink of each one to Yes? That might work better, if there are not other
text boxes on the same horizontal level that prevent the shrinking.

Alternatively, try moving the new line (carriage return and line feed
characters) to the end rather than the beginning of the lines. I don't know
that it will help, but seems worth trying... if final line that contains no
data is cut off, you won't suffer from whatever is causing this.

Larry Linson
Microsoft Access MVP

 

[Guest]

I believe I found the cause of my problem, but I am hoping if somebody can
verify this for me. I think that the problem is that I have the can grow
property set with a line spacing greater than 0. Putting a border around the
text box I could watch the size of the text box, and it was the same size as
I increased the line spacing. Hence, the text box control was growing to fit
the size of the data with 0 line space, but needed to be bigger due to the
increased line space and was then leaving out the last line of data.