Microsoft KB Archive/209562

= ACC2000: How to Create Reports to Mail Merge Microsoft Access Data =

Article ID: 209562

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209562



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
To perform a mail merge with Microsoft Access data, you can export or copy your data to a word processor program, or you can use the Microsoft Word Mail Merge Wizard. However, you can also create a Microsoft Access report to generate mail-merge documents. This article demonstrates a technique that uses a Microsoft Access report as the main document in a mail merge.



MORE INFORMATION
The following is an example of a business letter that you can create as a Microsoft Access report. All placeholder (italicized or bracketed) items represent values that can be filled in during the mail merge.

KB Advertising Agency

Dear ,

We are pleased to announce our new line of office furniture. As a  customer, you are entitled to a  % discount off our catalog prices.

Call us today toll-free at (800) 555-1212 and receive a free full-color catalog detailing all our top-of-the-line office products.

Sincerely,

J. Grant Manager, Office Products

How to Create the Sample Mail Merge
 Open a new, blank database.  Create the following new table and save it as Customer: Table: Customer --     Field Name: Salutation Data Type: Text Field Name: First Name Data Type: Text Field Name: Last Name Data Type: Text Field Name: Address Data Type: Text Field Name: City Data Type: Text Field Name: State Data Type: Text Field Name: ZIP Data Type: Text Field Name: Customer Type Data Type: Text Field Name: Discount Pct Data Type: Number   Open the table in Datasheet view and add the following records:      Salu-   First  Last                                     Cust   Disc tation Name   Name   Address   City       St    ZIP    Type   Pct ---     Mr.     John   Doe    123 Main  Middleton  OH    44044  Valued  10 Mrs.   Sally  White  52A Elm   Florence   KY    45123  Special  5 Dr.    Fred   Weiss  Box 456   Camden     SC    29332  Super   25  Create a new report in Design view based on the Customer table; to do so, follow these steps: NOTE: Do not use a Microsoft Access Report Wizard to create this report.

 On the View menu, click to clear the Report Header/Footer and Page Header/Footer check boxes; this removes those sections from the report.</li>  Set the following properties for the report's Detail section: <pre class="fixed_text">Section: Detail --- ForceNewPage: After Section CanGrow: Yes </li>  Add the following controls to the Detail section of the report, and then delete the label controls that are created by default with each text box control: <pre class="fixed_text">Label: Name: Company Label Caption: KB Advertising Agency Text Box: Name: Today's Date ControlSource: =Date Format: Long Date Text Box: Name: Name Line ControlSource: =[First Name] & " " & [Last Name] Text Box: Name: Address Line ControlSource: =[Address] Text Box: Name: CSZ Line ControlSource: =[City] & ", " & [State] & " " & [ZIP] Text Box: Name: Salutation Line ControlSource: ="Dear "&[Salutation]&" "&[Last Name]&"," Text Box: Name: Para 1 ControlSource: ="We are pleased to announce our new line of                 office furniture. As a " & [Customer Type] & "customer, you are entitled to a " & [Discount Pct] & "% discount off our catalog prices." CanGrow: Yes Label: Name: Rest of Letter Caption: Call us today toll-free at (800) 555-1212 and receive a free full-color catalog detailing all our top-of-the-line office products. Label: Name: Closing Caption: Sincerely, Label: Name: Grant Caption: J. Grant Label: Name: Title Caption: Manager, Office Products

NOTE: You can press CTRL+ENTER to force new lines in controls. For example, the Closing, Grant, and Title labels can be combined into a single label control by pressing CTRL+ENTER at the end of each line in the Caption property: <pre class="fixed_text">Label: Name: Closing Caption: Sincerely, <CTRL+ENTER> <CTRL+ENTER> <CTRL+ENTER> J. Grant <CTRL+ENTER> Manager, Office Products

</li></ol> </li> Size and position the controls on the report, and then set the font styles and sizes.</li> Save the report. Print or preview the report to see the results of the merge.</li></ol>

Summary and General Notes
<ul> Use label controls for all paragraphs with fixed text.</li> Use text box controls for all paragraphs with variable content.</li> Margins are determined by the width of the control. The text wraps within the width of the control, not the width of the report.</li> The Name property of a text box must be different from its field name in the table. If they are the same, the name appears as the following error message:


 * 1) Error?

</li></ul>

Limitations

 * You cannot change the formatting of individual words. You can change only the formatting of the control.
 * You are limited to 1024 characters in the ControlSource property of a text box. For long paragraphs, create one or more invisible text boxes to contain the text, and then concatenate them in a visible text box.

<div class="references_section">