ACC: How to Use a Query to Format Data for a Mail Merge
ID: Q191101
|
The information in this article applies to:
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
In a Microsoft Access table, if you've added formatting to the numbers
in a Number field and you send that data to Microsoft Word through a mail
merge, the numbers will lose their formatting when they appear in Microsoft
Word.
CAUSE
In a mail merge, only the actual data in the table is sent to Microsoft
Word. 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:
- Open the sample database Northwind.mdb.
- 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 would
cause a circular reference with the original field name.
- On the Query menu, click Run. Note that the Discount1 column shows a
percent (%) symbol next to each discount.
- On the File menu, click Save. Type MyTest1
in the Query Name box, and then click OK.
- On the File menu, click Close.
- Select the MyTest1 query in the Database window, but do not open it.
- On the Tools menu, point to Office Links, and then click Merge It With
MS Word.
- In the Microsoft Mail Merge Wizard dialog box, click to select
"Create 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 is
created.
- 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.
- Click Insert Merge Field, and then select UnitPrice. Press ENTER.
- Click Insert Merge Field, and then select Discount1. Press ENTER.
- 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 percent
sign is not lost.
MORE INFORMATIONSteps to Reproduce Behavior
The following example demonstrates what will occur when you include a field
that has a data type of Number and has a format specified in the table.
- Open the sample database Northwind.mdb.
- On the Tables tab, select Order Details, and then click Design.
- Click the Discount field and look at its properties. Note that its
data type is Number and its Format property is Percent.
- On the File menu, click Close.
- 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
- On the Query menu, click Run. Note that the Discount column shows a
percent symbol (%) beside each discount.
- On the File menu, click Save. Type MyTest2
in the Query Name box, and then click OK.
- On the File menu, click Close.
- Select the MyTest2 query in the Database window, but do not open it.
- On the Tools menu, point to Office Links, and then click
Merge It With MS Word.
- In the Microsoft Mail Merge Wizard dialog box, click to select
"Create 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 is
created.
- 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.
- Click Insert Merge Field, and then select UnitPrice. Press ENTER.
- Click Insert Merge Field, and then select Discount. Press ENTER.
- Click View Merged Data.
Note that the Discount field shows no percent sign (%), but you do
see the dollar sign ($) for UnitPrice. This is because in the table
definition, UnitPrice has a data type of Currency with no added
format. However, Discount has a data type of Number with an added
format of Percent. The mail merge only considers the data type.
REFERENCES
For more information about using the Microsoft Word Mail Merge feature,
click Contents And Index on the Help menu, click the Index tab in Microsoft
Access Help, type the following text
merging data
and then double-click the selected text to go to the "Merge Microsoft
Access data using the Microsoft Word Mail Merge Wizard" topic. If you are
unable to find the information you need, ask the Office Assistant.
Additional query words:
inf prb mailmerge
Keywords : kbdta IntpPrtm QryHowto
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
|