Microsoft KB Archive/287685

= How to concatenate fields in a text box to remove blank lines =

Article ID: 287685

Article Last Modified on 3/26/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287685



Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb or .accdb) and to a Microsoft Access project (.adp). For a Microsoft Access 2000 version of this article, see 209653.



For a Microsoft Access 97 version of this article, see 95917.



SUMMARY
This article shows you how to concatenate multiple fields for use in a form or a report. This is especially useful in a mailing label report when some fields are Null or empty. Null fields can cause blank lines to be printed on a report. Additionally, you may want to be able to copy and paste an entire name and address from a form into another application, such as Microsoft Word.



MORE INFORMATION
By using the IIf and the IsNull functions, you can determine if a field is blank. If a field is blank, it returns an &quot;empty&quot; value. Chr(13) and Chr(10) are used to add a return and a line feed character to the text box.

Follow these steps to create a concatenated field that eliminates blank lines. This example uses a form, but the same steps also apply to reports.  Open the sample database Northwind.mdb. Create a new form that is based on the Employees table, and open it in Design view.  Add a text box control to the detail section of the form, and then set the following properties.   Text Box ---  ControlName: Full Address ControlSource: =IIf(IsNull([FirstName]),&quot;&quot;,[FirstName] & &quot; &quot;) & _ IIf(IsNull([LastName]),&quot;&quot;,[LastName]& Chr(13)& Chr(10)) & _ IIf(IsNull([ADDRESS]),&quot;&quot;,[ADDRESS] & Chr(13) & Chr(10)) & _ IIf(IsNull([CITY]),&quot;&quot;,[CITY] & &quot;, &quot;) & _ IIf(IsNull([REGION]) ,&quot;&quot;,[REGION] & &quot; &quot;) & _ IIf(IsNull([PostalCode]),&quot;&quot;,[PostalCode]) CanGrow:  Yes CanShrink: Yes  Open the form in Form view. Note that there are no blank lines, even if some of the fields in the Employees table are blank.

<div class="references_section">