by Sal Ricciardi
Some addresses contain only a few lines, while others require an extra line or two. In this article, learn how to eliminate the blank lines that can appear in labels and reports when some addresses have more lines than others. We provide a sample function you can use to close up the unwanted space.
|Microsoft Office Access 2003
Microsoft Access 2000 and 2002
Microsoft Access 97
Question: How do I eliminate unwanted lines in a report, such as an empty line in an address?
You can often eliminate empty lines by setting the CanShrink property (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).) to Yes and carefully positioning your controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). As long as controls don't overlap from other lines and there's nothing else on the line, this will work. At other times, such as when you have a fixed graphic object that overlaps the area you'd like to shrink, you'll need to employ some custom Microsoft Visual Basic® for Applications (VBA) code to eliminate the blank lines. VBA is the programming language that Access uses. I'll introduce one solution that uses a custom VBA function later in this article.
Introducing the CanShrink and CanGrow properties
Access uses two properties to decide whether to adjust the size of a control to accommodate the data it contains — the CanGrow property and the CanShrink property. The CanGrow property tells Access whether to grow a control vertically when printed or previewed in order to display all the data in the control. The CanShrink property tells Access whether to shrink a control vertically when printed or previewed in order to remove empty space. You can set the CanGrow and CanShrink properties for text box controls, form and report sections, subform controls, and subreport controls.
To remove empty lines, you can set the CanShrink property for each text box on a line to Yes. The key point to remember is this: Access shrinks controls vertically, so the entire vertical area that bounds the text box must be empty. This means that if several controls exist on a line, they must all be empty for Access to automatically close up the space.
The default setting for the CanShrink property is No, so controls can't shrink unless you change this setting to Yes. If you also want the report section to shrink, you must set the report section's CanShrink property to Yes, too.
To ensure that all controls that should be empty are indeed empty, you must construct your control source expressions so that separating commas and punctuation are omitted when there's no data to separate or punctuate. This is good practice to begin with because stray commas can ruin the appearance of your report. Suppose, for example, you want to include the values in the City, Region, and PostalCode fields on a line — with a comma following City. Consider the following example control source.
=Trim([City] & (", " + [Region]) & " " & [PostalCode])
This example assumes that the City field always contains data, but the Region field might not. The expression places the comma after City only when data for Region exists; otherwise a space is inserted. The following part is key.
(", " + [Region])
This works because using the + operator instead of the & operator to combine strings causes Access to perform what's called "null propagation." This means if any part of the expression contains null, the entire expression is null. Thus, if [Region] contains null, the entire contents enclosed in parentheses are considered null, and the comma is removed. In this way, the insertion of a stray comma is prevented when the value in the Region field is empty, which helps to ensure a blank line when all fields contain no data. Access can then shrink away the blank line if Can Shrink is set to Yes.
Note that this works only if the Region field contains null when the value is missing. If you set the Allow Zero Length property for the field to Yes to allow empty strings in your field (generally not recommended), you'll need a different expression for your control source, as in the following example.
=Trim([City] & IIf([Region] Is Null Or [Region]="",
" ",", " & [Region] & " ") & [PostalCode])
This expression uses the IIf function to determine if [Region] is empty and, therefore, if the comma is required or not.
Keep the following points in mind when trying to create controls that shrink vertically as needed:
- If controls overlap in any way within their vertical boundaries, even for one pixel, they will not shrink unless they are all empty.
- The blank space between controls is not affected by the CanShrink property. This means that if there's vertical space between controls, that space will remain even after the surrounding controls shrink.
- Controls located in the page header or page footer section can shrink. However, these sections do not themselves have the CanShrink property, so the page header and page footer sections cannot shrink.
When controls overlap
Using the CanShrink property won't be useful if another control overlaps the vertical space used by the text box control, such as a fixed graphic object containing a logo. In this case, there's no way that the entire vertical line can ever be empty, so you can't rely on Access to do this automatically for you. Here, you might be able to do the shrinking yourself. To do so, you use a single text box for the entire block of lines, and then use the CanShrinkLines function, as shown here, to combine the contents of the lines. For example, consider the following control source for a text box.
Trim([City]) & (", " + [Region]) & " " & [PostalCode],[Country])
The custom function CanShrinkLines, shown below, automatically creates a line for each argument, except when that argument is empty or null. By ignoring empty or null arguments, the function automatically removes unwanted lines. You pass one argument or expression per line to this function, separated by commas. If a field is empty, it's ignored and the fields that follow move up a line.
Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, Address1, Address2, City, St, Zip)
Dim x As Integer, strLine As String
For x = 0 To UBound(arrLines)
If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then
strLine = strLine & arrLines(x) & vbCrLf
CanShrinkLines = strLine
The CanShrinkLines function works by testing each argument in the argument array to determine if it is either null or equal to an empty string (""). Technically, a null and an empty string are not the same because a null represents the absence of data, while an empty string represents the presence of data in the form of an empty string. For our purposes, either one results in an empty line to be removed.
If the argument is neither null or an empty string, the function creates a line out of it by appending a carriage return and line feed by using the VBA constant vbCrLf, and then appends the line to any existing lines. This forms an eventual result that is one long string consisting of some number of lines, none of them empty.
Creating the function
To create the CanShrinkLines function yourself, start Access and follow these steps:
- On the File menu, click Open, browse to the database in which you want to insert the function, and then double-click the file to open it.
- In the Database window, click Modules.
- Click New to create a new module.
- In the Code window, type the CanShrinkLines function exactly as you see it earlier in this article.
- On the File menu, click Save.
- Type a module name, such as MainModule, and then press ENTER.
- On the Debug menu, click Compile.
- On the File menu, click Close and Return to Microsoft Office Access.
After you place the function in your database, you can use it in Access objects such as forms and reports.
Employing the function in reports
Using the CanShrinkLines function is easy. Let's suppose you want to create some business address labels that include a logo on the left and the address on the right. Because the logo will overlap the space for the address, you can't rely on using the CanShrink property by itself. Instead, create a single text box for the address and change the Control Source property to something like the following.
[Address2],Trim([City]) & (", "+[Region]) & " " & [PostalCode],
The design of the report and the resulting label might look like the following.
For more information
- For a varied and extremely useful collection of solutions and suggestions for Access, including information on eliminating blank lines, see Getz, Ken; Litwin, Paul; Baron, Andy. Access Cookbook. O'Reilly Media, Inc., 2003.
- For additional information about eliminating white space in Access reports, see Eliminate white space in Access reports with CanShrink property and VBA code.