ACC: Creating Reports to Mail Merge Microsoft Access Data (98798)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q98798
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

To use a mail merge with Microsoft Access data, you can export or copy your data to a word processor, or in Microsoft Access 2.0, 7.0 and 97, you can click the Merge It button on the toolbar. However, you can also create a Microsoft Access report to generate mail-merge documents. This article shows 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 values in angle brackets (<>) are items 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. Create 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:

    NOTE: Do not use a Microsoft Access Report Wizard to create this report.
    1. On the View menu, (or the Layout menu in version 1.x, or the Format menu in version 2.0), clear the check marks next to Report Header/ Footer and Page Header/Footer to remove those sections from the report.
    2. Delete the label controls that are created by default with each text box control.
    3. Set the following properties of the report's Detail section: 
               Section: Detail
         ---------------------------
         ForceNewPage: After Section
         CanGrow: Yes
    4. Add the following controls to the Detail section of the report: 
               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, <press CTRL+ENTER>
                  <press CTRL+ENTER>
                  <press CTRL+ENTER>
                  J. Grant <press 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 will wrap within the width of the control, not the width of the report.
  • The Name of a text box must be different from its field name in the table. If they are the same, the Name prints as a "#Error?" message.

Limitations


  • You cannot change the formatting of individual words. You can change only the formatting of the control.
  • You are limited to 255 characters in the ControlSource property of a text box. For long paragraphs, create one or more invisible text boxes to contain the text, then concatenate them in a visible text box.
Modification Type:MajorLast Reviewed:5/28/2003
Keywords:kbinfo KB98798
©2002 Microsoft Corporation. All rights reserved.