Managing Cursors, Quotes, Subforms,and Missing Data

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Mar-2003 >

Managing Cursors, Quotes, Subforms,and Missing Data

Christopher Weber

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

like this:


=([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 ....