Wherein Christopher Weber looks at four problems:
handling missing data in reports, customizing the cursor,
variable sized subforms, and managing quotes.
I have a billing report that displays our customer’s billing
information on the left in a single column, and our company
logo on the right. Some customers have single street
address lines, while others have two. I need the second
address line to shrink for those customers who don’t use it
and have the city, state, and ZIP move up to replace it. I’ve
set the Can Shrink property of the address2 text box to Yes,
but it won’t work because of the logo to its right. Is there a
way to force the text box to shrink anyway?
I recently ran across this same problem when printing a
series of tax forms. At first, one of my developers tried to
set the visible property of the second street text box to
False and then move the city, state, and ZIP up when
needed. After reviewing the proposed solution, I came up
with a simpler answer that avoids control names and is a
general solution to the problem.
My first attempt was to use the conditional IIf( )
function to insert a carriage return and line feed
characters between the addresses inside a single control
when both controls weren’t Null. The resulting
ControlSource property for the single text box displaying
address1, address2, and city, state, ZIP looked something
=([street1] & Chr(10) & Chr(13)) & _
IIf(IsNull([street2]),"",[street2] & Chr(10) & _
Chr(13)) & ([city] & ", " & [state] & " " & _
The idea was right, but the results were all wrong.
Figure 1 shows a pair of cells produced by using the IIf
statement in a query: The carriage return/line feed
characters (Chr(10) & Chr(13)) appear as small boxes in
the output—not an acceptable answer. I tried using the
intrinsic vbCrLf constant, but immediately realized that it ....