Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to concatenate fields in a text box to remove blank lines

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

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 .

↑ Back to the top

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.

↑ Back to the top

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 "empty" 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.

  1. Open the sample database Northwind.mdb.
  2. Create a new form that is 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 then set the following properties.

       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])
       CanGrow:   Yes
       CanShrink: Yes
  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.

↑ Back to the top

References

For more information about the IIf() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type IIf() in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the IsNull() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type IsNull() in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about working with fields that contain no data, click Microsoft Access Help on the
Help menu, type About working with blank fields in queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: kbhowto, kbdta, kbaccess10ready, accrtmpublic, kboffice2003yes, kbbillprodsweep, kb, kbsweptsoltax, kbfreshness2006, kboffice12yes, kbswept

↑ Back to the top

Article Info
Article ID : 287685
Revision : 3
Created on : 4/23/2018
Published on : 4/23/2018
Exists online : False
Views : 205