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

Article translations Article translations
Article ID: 209562 - View products that this article applies to.
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).

Expand all | Collapse all

On This Page

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

February 5th, 1993

John Doe
123 E. Main Street
Middle Town, OH 44044

Dear Mr. Doe,

We are pleased to announce our new line of office furniture. As a valued customer, you are entitled to a 10% 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

  1. Open a new, blank database.
  2. 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
    					
  3. 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
    					
  4. 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.

    1. On the View menu, click to clear the Report Header/Footer and Page Header/Footer check boxes; this removes those sections from the report.
    2. Set the following properties for the report's Detail section:
      Section: Detail
      ---------------------------
      ForceNewPage: After Section
      CanGrow: Yes
      						
    3. 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:
      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:
      Label:
      Name: Closing
      Caption: Sincerely, <CTRL+ENTER>
      <CTRL+ENTER>
      <CTRL+ENTER>
      J. Grant <CTRL+ENTER>
      Manager, Office Products 
      
      						
  5. Size and position the controls on the report, and then set the font styles and sizes.
  6. Save the report. Print or preview the report to see the results of the merge.

Summary and General Notes

  • Use label controls for all paragraphs with fixed text.
  • Use text box controls for all paragraphs with variable content.
  • Margins are determined by the width of the control. The text wraps within the width of the control, not the width of the report.
  • 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:
    #Error?

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.

REFERENCES

For more information about merging data using the Microsoft Word Mail Merge Wizard, click Microsoft Access Help on the Help menu, type merge data from a table or query by using the microsoft word mail merge wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209562 - Last Review: June 28, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo KB209562

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com