You are currently offline, waiting for your internet to reconnect

ACC2000: How to Use a Query to Format Data for a Mail Merge

This article was previously published under Q197520
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS
In a Microsoft Access table, if you've added formatting to the numbersin a Number field, and you send that data to Microsoft Word through a mailmerge, the numbers will lose their formatting when they appear in MicrosoftWord.
CAUSE
In a mail merge, only the actual data in the table is sent to MicrosoftWord. Any formatting is not sent.
RESOLUTION
In a query, use the Format() function in an expression to create a text string of formatted data from the field. You can then base the mail merge on that query and see the formatting. To send formatting to Word through mail merge, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create the following query:
       Query: MyTest1   -----------------------------------------   Type: Select Query   Field: OrderId      Table: Order Details      Criteria: 10252   Field: UnitPrice      Table: Order Details   Field: Discount1: Format([Discount],"0%")						
    Note that the last field cannot be called Discount because it wouldcause a circular reference with the original field name.
  3. On the Query menu, click Run. Note that the Discount1 column shows a percent (%) symbol next to each discount.
  4. On the File menu, click Save. Type MyTest1 in the Query Name box, and then click OK.
  5. On the File menu, click Close.
  6. Select the MyTest1 query in the Database window, but do not open it.
  7. On the Tools menu, point to Office Links, and then click Merge It With MS Word.
  8. In the Microsoft Mail Merge Wizard dialog box, click to selectCreate a new document and then link the data to it, and then click OK.

    Note that Microsoft Word starts and that a new, blank document iscreated.
  9. In Word, click Insert Merge Field. Note that a list of the fields in the MyTest1 query are displayed. Select OrderID, and then press ENTER.
  10. Click Insert Merge Field, and then select UnitPrice. Press ENTER.
  11. Click Insert Merge Field, and then select Discount1. Press ENTER.
  12. Click View Merged Data.
Note that the Discount1 field has a percent sign (%) for all the records.Because the expression in the query evaluates to a text string, the percentsign is not lost.
MORE INFORMATION

Steps to Reproduce Behavior

The following example demonstrates what will occur when you include a fieldthat has a data type of Number and has a format specified in the table.
  1. Open the sample database Northwind.mdb.
  2. Click Tables under Objects, click the Order Details table, and then click Design.
  3. Click the Discount field, and then look at its properties. Note thatits data type is Number and its Format property is Percent.
  4. On the File menu, click Close.
  5. Create the following query:
       Query: MyTest2   -----------------------   Type: Select Query   Field: OrderId      Table: Order Details      Criteria: 10252   Field: UnitPrice      Table: Order Details   Field: Discount      Table: Order Details					
  6. On the Query menu, click Run. Note that the Discount column shows a percent symbol (%) beside each discount.
  7. On the File menu, click Save. Type MyTest2 in the Query Name box, and then click OK.
  8. On the File menu, click Close.
  9. Click the MyTest2 query in the Database window, but do not open it.
  10. On the Tools menu, point to Office Links, and then click Merge It With MS Word.
  11. In the Microsoft Mail Merge Wizard dialog box, click to selectCreate a new document and then link the data to it, and thenclick OK.

    Note that Microsoft Word starts and that a new, blank document iscreated.
  12. In Word, click Insert Merge Field. Note that a list of the fields in the MyTest2 query are displayed. Select OrderID, and then press ENTER.
  13. Click Insert Merge Field, and then select UnitPrice. Press ENTER.
  14. Click Insert Merge Field, and then select Discount. Press ENTER.
  15. Click View Merged Data.

    Note that the Discount field shows no percent sign (%), but you dosee the dollar sign ($) for UnitPrice. This is because in the tabledefinition, UnitPrice has a data type of Currency with no addedformat. However, Discount has a data type of Number with an addedformat of Percent. The mail merge only considers the data type.
REFERENCES
For more information about the Microsoft Word Mail Merge feature, click Microsoft Access Help on the Help menu, type Merge data from a table or query by using theMicrosoft Word Mail Merge Wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
inf prb mailmerge
Properties

Article ID: 197520 - Last Review: 07/13/2004 18:04:00 - Revision: 1.0

  • Microsoft Access 2000 Standard Edition
  • kbprb KB197520
Feedback