ACC2000: Concatenating Fields in a Text Box to Remove Blank Lines

ID: Q209653

The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).


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.


MORE INFORMATION

By 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.

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


    4. 
   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
  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 the IIf() function, click Microsoft Access Help on the Help menu, type "IIf()" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the IsNull() function, click Microsoft Access Help on the Help menu, type "IsNull()" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

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.

Additional query words: whitespace ampersand labels 


Keywords          : kbusage kbdta FmrHowto 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: May 13, 1999