ACC: Concatenating Fields in a Text Box to Remove Blank Lines (95917)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q95917
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

This article describes how to concatenate multiple fields for use in a form or 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, from a form, you may want to be able to copy and paste an entire name and address into another application, such as Microsoft Word for Windows.

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

MORE INFORMATION

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.
  1. Open the sample database Northwind.mdb (or Nwind.mdb in Microsoft Access 1.x or 2.0).
  2. Create a new form based on the Employees table and open it in Design view.
  3. Add a text box control to the detail section of the form and set the following properties.

    NOTE: In the following example, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example. 

          Text Box
      ---------------------------------------------------------------
      ControlName: Full Address
      ControlSource:
         =IIf(IsNull([FirstName]),"",[FirstName] & " ") & _
         IIf(IsNull([LastName]),"",[LastName]& Chr(13)& Chr(10)) & _
         IIf(IsNull([ADDRESS]),"",[ADDRESS] & Chr(13) & Chr(10)) & _
         IIf(IsNull([CITY]),"",[CITY] & ", ") & _
         IIf(IsNull([REGION]) ,"",[REGION] & " ") & _
         IIf(IsNull([PostalCode]),"",[PostalCode])
      Can Grow:   Yes
      Can Shrink: Yes

    NOTE: In versions 1.x and 2.0, replace all instances of [FirstName], [LastName], and [PostalCode] in the previous example with [First Name], [Last Name], and [Postal Code].
  4. 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.

REFERENCES

For more information about using IIf() with Null values, type "IIf" in the Office Assistant, click Search, and then click to view "About combining text values from fields when some fields contain no data."
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbhowto kbusage KB95917
©2002 Microsoft Corporation. All rights reserved.