Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 287685 - Last Review: March 26, 2007 - Revision: 6.1
How to concatenate fields in a text box to remove blank lines
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=209653/EN-US/
)
.
For a Microsoft Access 97 version of this article, see
95917Â
(http://kbalertz.com/Feedback.aspx?kbNumber=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 "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.
- 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]),"",[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
- 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, 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.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please