Force Word to use the number format you want

As long as Word uses DDE to link to a data source, it will usually bring across numeric or date formatting as you see it. Word only uses DDE for Excel or Access, and by default only in Word 2000 and earlier versions.

For other database types, ODBC was standard until the advent of Word 2002, when OLE DB was introduced. For both ODBC and OLE DB connections, Word picks up only the data, and none of the formatting stored in the application interface. This means, you need some other way of displaying numerical values and dates in the format you want.

One method for doing this is to add formatting switches to the field codes. Display the field codes and add a number formatting switch to the field.

Example:

{ Mergefield Cost \# "0.00" }
Display: 101.00

Example:

{ Mergefield Phone \# "(000)'-'000'-'0000" }
Display: (999)-999-9999

An alternative to formatting the numbers in Word is to pass the information to Word as a string by formatting the number in a query, then linking the mail merge to the query. Here's an example for an expression in an Access query, using the Format function on the Phone field:

PhoneNr: Format([Phone], "(000)-000-0000")

You can view a picture of the Access QBE grid (PHFormat.gif, 16 KB)

In Excel, you have to search a bit more intensively to discover a solution. Simply applying a text formatting to a cell doesn't work: this formatting does not affect the data type and is stored only in the Excel interface.

  1. Select the numerical column
  2. Go to the Datamenu
  3. Select Text to Columns
  4. Click Next until you reach Step 3
  5. Activate the Text option button
  6. Click Finish.

Mail merge loses Excel data

When ODBC is used to make the link - and OLE DB uses Excel's ODBC driver, since Excel has no OLE DB provider of its own - the first eight rows are scanned in order to set the data type for the columns (fields). If a column contains mixed numerical and non-numerical values, the field contents are suppressed when they don't match the data type the ODBC driver has determined. This means, these fields are empty in the mail merge, and other problems (incorrect field values) result, as well.

Here, again, the best solution is to pass numerical data and dates as text.


Change the merge result date format

Display the field codes and add a date formatting switch to the field.

Example:

{ Mergefield Date \@ "dd-MMM-yyyy"
Display: 01-JAN-1998

Example:

{ = { Mergefield Date \@ "d" } \* ordinal } { Mergefield Date \@ "MMMM, yyyy" }
Display: 1st January, 1998

Notice the uppercase M for the month; lowercase m displays the minutes. You must enclose the date format in "double-quotes".

The general remarks about number formats also apply to date formats.


Get mail merge to show the merge results in the same font in which the mergefield is formatted

Display the field codes and add the character formatting switch to the field.

Example: { Mergefield Name \* CharFormat }


Carry over the special font formatting you may have in the Word data source (Word table)

Display the field codes and remove word "mergefield" from the field, leaving only the field name. In the example, the content of the Remarks field is Arial, and yellow.

Example: { Remarks }
Display: This is an excellent place to be.

Starting with Word 97, the main merge document must contain at least one field that is labelled specifically Mergefield, else the mail merge will not execute and you won't be able to view the data.

Superscripts and subscripts will NOT be maintained using this method. Instead, you must use the Equation Editor. For further reference, see KB article Q116220


Change the case of the mail merge result

Display the field codes and add the appropriate formatting switch

\* upper UPPER CASE
\* lower lower case
\* Caps Title Case

Example:

{ Mergefield Name \* upper }
Display: MEISTER


Spell check the results of merge fields

By default, the result of many fields is formatted as "no proofing"; and if you look in Tools/Language/Set language you'll see that the checkbox "Do not check spelling or grammar" is activated. There are times, however, when you want to be able to spell check the results of mail merge fields. While you can press Ctrl+A in the mail merge result document and then deactivate "Do not check spelling and grammar", there is another way.

  1. Create a new CHARACTER style in your document and make sure it is set to use the language you prefer
  2. Alt+F9 to view the field codes
  3. Select a field where you want spell check to work and press Ctrl+F9. This inserts { } around the selection
  4. Type QUOTE just inside the { opening bracket
  5. Type \* CharFormat just inside the closing bracket }
  6. Apply the character style to the QUOTE field's content

Repeat 2 - 6 for all fields you want to spell check. Alt+F9 again to hide the field codes. Example:
{ QUOTE { Mergefield Comments } \* CharFormat }

The \* CharFormat switch forces a field to display with the character formatting applied to the first character in the field. This doesn't work on a merge field, however. But it does work on a Quote field. So if you nest the merge field in a Quote field that contains this switch, then apply a character style to the field that uses your language, spell check should work.


Display database Yes/No information as Yes/No
Display any Yes/No information as a checkbox

Most database sources store Yes/No information as 0 (=No) and -1 (or 1; = Yes) - and this is how Word will display the contents of a Yes/No type of mergefield in a merge. You can display anything you want by using an IF field.

Example:

{ IF { Mergefield YesNo } = "0" "No" "Yes" }
Display: Yes

Word 2002 will occasionally pass Access 2002 database Yes/No results as the literal text "Yes" and "No". Watch out for this if you're convering mail merges from earlier versions.

If you'd prefer to display an unchecked or checked checkbox you can insert the corresponding symbols in the IF field instead of the "No" and "Yes" in the example.

Go to the section on field codes for more information about working with fields.


Working with field codes

Display field codes

Press Alt+F9 to switch between displaying the field codes and field results.

If field codes are printing, rather than field results, deactivate "Field codes" in Tools/Options/Print.

Insert field brackets { }

Field brackets will be inserted automatically if you use the Insert/Field command.

You can also insert them manually by pressing Ctrl+F9.

Update the field display

You can force an update of the field display by clicking on it and pressing F9. All the fields in a document can be updated by pressing Ctrl+A, then F9.

Copy a field code as normal text

When you need to prepare documentation that discusses field codes, such as this web-site, it can be very tricky if you want to show examples of field codes. Because when you copy and paste a field code, what you normally get is not the text, but the field result! The following two macros will copy the field you currently have selected in your document and either insert it as text (Word 6/7) or place it on the clipboard so that it can be pasted anywhere (Word 97).

Sub FieldCodeToString()
'Macro code for Word97
Dim Fieldstring As String, NewString As String
Dim CurrSetting As Boolean, fcDisplay As Object
Dim MyData As DataObject
NewString = ""
Set fcDisplay = ActiveWindow.View
Application.ScreenUpdating = False
CurrSetting = fcDisplay.ShowFieldCodes
If CurrSetting <> True Then fcDisplay.ShowFieldCodes = True
Fieldstring = Selection.Text
For X = 1 To Len(Fieldstring)
CurrChar = Mid(Fieldstring, X, 1)
Select Case CurrChar
Case Chr(19)
  CurrChar = "{"
Case Chr(21)
  CurrChar = "}"
Case Else
End Select
NewString = NewString & CurrChar
Next X
Set MyData = New DataObject
MyData.SetText NewString
MyData.PutInClipboard
fcDisplay.ShowFieldCodes = CurrSetting
End Sub

Sub MAIN
'Macro code for Word6/7
ScreenUpdating 0
CurrSetting = ViewFieldCodes()
If CurrSetting <> - 1 Then ViewFieldCodes 1
Fieldstring$ = Selection$()
NewString$ = ""
For X = 1 To Len(Fieldstring$)
CurrChar$ = Mid$(Fieldstring$, X, 1)
Select Case CurrChar$
Case Chr$(19)
  CurrChar$ = "{"
Case Chr$(21)
  CurrChar$ = "}"
Case Else
End Select
NewString$ = NewString$ + CurrChar$
Next X
CharRight : InsertPara
Insert NewString$
ViewFieldCodes CurrSetting
End Sub


Displaying "Double Quotes" in Word field results

Usually, result texts in Word fields such as ASK, FILLIN, IF, SET, etc. are "contained" by sets of double quotation marks. This means that you can't display double quotation marks in such text simply by typing them in - you have to trick Word into displaying them.

BulletWord 6 / 7 (95)

Precede the double quote (or any other special, reserved character) with a backslash:

{ FILLIN "Please type your \"name\" quotes" }

BulletWord 8 (97), Word 2002 and Word 2003

Another one of those little things that got "broken" in the newest version of Word. The Word 6/95 example does work, for instance, but if you want to use double quotes in an IF field, you need to use the SYMBOL field to generate the double quotes:

{ IF True = True "This is { Symbol 147 }a test{ Symbol 148 }." "nothing" }

Note, however, for Word 2002 and 2003 that the Symbol field will only work if there is a space on at least one side.

BulletWord 2000

Consistency across versions is, in the case of special fields, NOT a given. For Word 2000  you need to enclose the Symbol field in a Quote field.

{ IF True = True "This is { Quote "{ Symbol 147 }a test{ Symbol 148 }.}" "nothing" }

Interestingly enough, if you try to use the SYMBOL field in the FILLIN field example you get nothing at all...


Obtaining Microsoft Knowledge Base Articles

Knowledge Base articles can be viewed on the Microsoft web-site:
http://www.microsoft.com

The exact address can be derived using the following formula:
http://support.microsoft.com?kbid=nnnnnn

In November 2002, Microsoft removed the letters that identifed a Knowledge Base article by language (Q for English, D for German, etc.) All articles the cover the same topic have the same number; your browser will automatically take you to the local language article for the browser inteface, unless you specify differently by prefixing the number with a country code: en-us for English, de for German, etc.

http://support.microsoft.com?scid=kb;de;nnnnnn


Return to top of this article

Return to Home Page