Field codes use in mail merge

For the basics, see:
http://office.microsoft.com/en-us/wo...02110133.aspx?
http://office.microsoft.com/en-us/wo...101830917.aspx
Granted, the above are on-line, but you could print them.

For some advanced stuff - refer to the following for lots of complex coding but not much explanation of how they work.

To see how use fields to do just about everything you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
http://windowssecrets.com/forums/sho...ation-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party

To see how to do a wide range of mathematical calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
http://windowssecrets.com/forums/sho...Maths-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party

To see how to group mailmerge data with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...merge-Tutorial
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

You're unlikely to find much that falls between the basics and the advanced stuff. However, you may also find the following useful:

Convert Mailmerge Date Fields to Plain Text
The DATE field ordinarily survives the mailmerge process and remain active in the output document, which can be problematic, since such fields update to the current date every time you open the document. Conversely, CREATEDATE fields from the mailmerge main document reflect that document's creation date (not that of the mailmerge output).
To force DATE fields to convert to their results (ie so that they become static dates), reflecting the date on which the merge was performed, simply embed them in a QUOTE field coded along the lines of:
'{QUOTE{DATE}}'
plus any formatting switches you might want for the date format.

Managing Mailmerge Graphics
To insert variable images in a mailmerge, you need to embed the relevant mailmerge field in a INCLUDEPICTURE field. However, there are some issues with this that make the process less straightforward than one might expect. For example, when embedding a mailmerge field in an INCLUDEPICTURE field for the purpose of merging graphics:
1. the file paths to the fields need to have the separators expressed as '\\' or '/' instead of the usual '\';
2. the pictures usually won't show until you refresh the fields (eg Ctrl-A, then F9) in the output document after completing the merge; and
3. even after updating the images, they remain linked to the image files, which can be an issue if you later delete the image or you need to send the merged output to someone else.

The following field construction addresses all three issues (i.e. you don't need to do anything special to the paths, or refresh the fields, and they'll no longer be linked to the source files):
{IF{INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d}}
After running a mailmerge coded this way, you'll have the correct, unlinked image for each record.

This form of field construction can be very useful where the filepath for the images is held in the mailmerge data source, in which case you could use:
{IF{INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d}}

Note: You need a path separator between the filepath mergefield and the image mergefield. If that separator is included in the source data, it can be omitted from the field above construction but leaving it there has no adverse effects
either.

If you can be sure the pictures will always be in the same folder as the mailmerge main document, you can incorporate a FILENAME field thus:
{IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d}}

If the path data are included with in the image field, you can use:
{IF{INCLUDEPICTURE {IF TRUE «Image»} \d} {INCLUDEPICTURE {IF TRUE «Image»} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d} {INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d}}
For what it's worth, *provided* the path has the separators expressed as '\\' or '/', you can retain the links by omitting the all-encompassing IF test and the images will display correctly without the need to refresh the fields after completing the merge. For example:
{INCLUDEPICTURE {IF TRUE "C:\\Users\\My Document Path\\Pictures\\«Image»"} \d}

Mailmerge Hyperlink ‘Click Here’ Insertion
By default, if you insert a mailmerge field into a hyperlink field, the hyperlinks will all show the first record’s address as the 'Text to display' text. Here's how you can do get a mailmerge to display your preferred default 'Text to display' text instead:
1. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal way, choosing whatever 'Click Here' text you want in the 'Text to display' box.
2. Select the inserted hyperlink and press Shift-F9 to expose its field code.
3. Replace everything in the field after 'HYPERLINK' with your mergefield.
4. Select the field and press F9 to update the display.

In Word 2007 & later, you can make the display text variable also, by following these additional steps:
5. Position the cursor anywhere within the display text.
6. Insert a mergefield pointing to whatever data field you want to use for the display text (this could even be the same field as used at step 3 above).
7. Delete all of the previous display text either side of your last-inserted mergefield (note that this field will likely have updated already).
8. Execute the merge.
9. After merging to a new document, use Ctrl-A, F9 to update all fields. Without this, the mergefield hover text won’t update to the correct targets.

NOTE WELL: The field brace pairs (i.e. '{ }') for the above examples are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field constructions are all required.

Mailmerge CheckBox Insertion
To use a mergefield to toggle the state of a checkbox, insert an IF field coded as:
{IF{MERGEFIELD CheckState}= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
or:
{IF«CheckState»= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
where 'CheckState' is the name of the mergefield used to determine the checkbox status and 'X' is the field value that toggles it 'checked'.

Mailmerge String Formatting
to control mailmerge string formatting, add a 'Charformat' picture switch to the mergefield as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
• delete everything between 'MyData' and the closing field brace;
• add ' \* Charformat' after 'MyData', so that you end up with {MERGEFIELD MyData \* Charformat};
• format at least the 'M' in 'MERGEFIELD' with the font attributes you want;
• position the cursor anywhere in the field and press F9 to update it.
• run your mailmerge.

Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
Note1: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
• \# 0 for rounded whole numbers
• \# ,0 for rounded whole numbers with a thousands separator
• \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
• \# $,0 for rounded whole dollars with a thousands separator
• \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
Note2: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. \# "$,0.00") and and insert a tab into the field code after the $ sign, you can have the values output like:
$ 999,999.99
$ 9,999.99

Force '0' Substitution for Empty/Missing Records
You can force the substitution of $0.00 for missing records via the following process:
1. Select your mergefield. For the purposes of this example, I'll suppose it's named 'Value' and shows up as '«Value»' in your mailmerge main document.
2. Press Ctrl-F9 to embed your mergefield in another field, so that you get '{«Value»}'.
3. Type 'Set Val' into the '{«Value»}' field, so that you end up with '{Set Val «Value»}'
4. After the newly-inserted field, press Ctrl-F9 again to insert another (empty) field, so that you get '{Set Val «Value»}{ }'
5. Type '=Val \# "$,0.00"' into the second field, so that you end up with '{Set Val «Value»}{=Val \# "$,0.00"}'
6. Run your mailmerge.

Basic Mailmerge Maths
You can create the formula in Word to perform maths on a mergefield. For example, suppose your data include the final price of an item for which you need to show how much is is the base price and how much is the tax component, where the tax is 10% of the base price (ie 1/11th of the final price):
• select your mergefield, which will look something like «Price»;
• press Ctrl-F9 to wrap another field around it, thus { «Price» };
• to calculate the tax component, edit the field so that you get {=«Price»/11 \# "$,0.00"};
• to calculate the base price, edit the field so that you get {=«Price»*10/11 \# "$,0.00"};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

Mailmerge Percentage Formatting
To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
• select your mergefield, which will look something like «Percent»;
• press Ctrl-F9 to wrap another field around it, thus { «Percent» };
• edit the field so that you get {=«Percent»*100 \# 0.00%};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

Mailmerge Phone Number Formatting
To control Phone Number formatting in Word, all you need to do is to add a picture switch to the mergefield, as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
• delete everything between 'MyData' and the closing field brace;
• add ' \# "(000) 0000 0000"' after 'MyData', so that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge Date Formatting
Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB connection by default, though you can change this (to DDE, for example). To work around a limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yyyy format, regardless of the format in Excel, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

To get the date format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your dates will come out like '2 August 2008'.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy".
Note: You can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

One thing you'll also notice if you're using ordinary date fields in the mailmerge main document is that the date in the output document will update if you re-open the document at a later date, which can be problematic. There is a simple way to prevent this - embed the Date field in the mailmerge main document in a QUOTE field. To do this, simply select the date field, press Ctrl-F9 to embed it in another field, and type 'QUOTE' inside the left field brace, so that you end up with {QUOTE 'date field'}. With the QUOTE field, the date field will automatically unlink when the mailmerge output document is created.

For any existing letters, you may be able to retrieve the original date by changing the DATE fields to CREATEDATE or PRINTDATE fields. To do this, open the document and press Alt-F9 to expose the field codes. Now do a Find/Replace, to change all instances of 'DATE' to either 'CREATEDATE' or 'PRINTDATE'. Use 'CREATEDATE' if the file was saved using Save As, or 'PRINTDATE' if the file wasn't saved that way but was printed at that time (before saving) and hasn't been saved again since any subsequent re-prints. Then press Alt-F9, Ctrl-A, F9, to toggle the field codes back to the normal display and update them. If the output is now correct, press Ctrl-A, Ctrl-Shift-F9 to convert them to plain text.

Mailmerge Time Formatting
To get the time format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyTime} where 'MyTime' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"}.
Note: you must use lowercase 'm's for minutes - uppercase 'M's are for months ;
• if you want the hours and/or minutes to display leading 0s, change 'h' to 'hh for hours and 'm' to 'mm' for minutes;
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge US Social Security Number Formatting
The following field suppresses all except the last four digits in a mergefield that gets its data from a field named ‘SSN’, where the data are formatted as ‘123-45-6789’.
{QUOTE
{SET ID {MERGEFIELD SSN}}
{SET Part3 {=({ID}*(-1)-ID)/2}}"XXX-XX-"{Part3 \# 0000}}

Mailmerge US Zip Code Formatting
The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Mailmerge String Testing
You can test the contents of a mergefield by doing the following:
• select the field and Press Ctrl-F9 to enclose it in a new field, thus {«MyData»};
• fill in around the new field braces so that you end up with: {IF«MyData»= "Test String" "String/field to output if True" "String/field to output if False"};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge Number/Text Differentiation
You can test the contents of a mergefield for text vs numbers by doing the following:
• select the field and Press Ctrl-F9 twice to enclose it in a pair of fields, thus { { «MyData» } };
• between the last pair of field braces press Ctrl-F9 twice to generate another pair of fields so that you end up with: { {SET Val «MyData» }{ { } } };
• before the new last pair of field braces press Ctrl-F9 to generate another field so that you end up with: { {SET Val «MyData» }{ { }{ } } };
• fill in around the new field braces so that you end up with: {QUOTE{SET Val «MyData»}{IF{Val}={=Val} "String/field to output if Numeric" "String/field to output if Text"}};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge Empty Space Suppression
You can use the mergefield \b and/or \f switches to suppress a space before or after an empty mergefield.
Suppose you have:
«Title» «FirstName» «SecondName» «LastName»
but «SecondName» is sometimes empty. To deal with that:
• select the «SecondName» field and press Shift-F9 so that you get {MERGEFIELD SecondName};
• edit the field code so that you end up with {MERGEFIELD SecondName \f " "} or {MERGEFIELD SecondName \b " "}, depending on whether the space to be suppressed is following or before the mergefield;
• delete, as appropriate, the corresponding space following or before the mergefield;
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge Empty Line Suppression
You can use the mergefield \b and/or \f switches to suppress a paragraph break (or a line break) before and/or after an empty mergefield.
Suppose you have:
«Title» «FirstName» «LastName»
«CompanyName»
«Address1»
«Address2»
«City», «State» «Zip»
but «Address2» is sometimes empty. To deal with that:
• select the «Address2» field and press Shift-F9 so that you get {MERGEFIELD Address2};
• edit the field code so that you get {MERGEFIELD Address2 \f ""} or {MERGEFIELD Address2 \b ""}, depending on whether the line to be suppressed is following or before the mergefield;

• move the paragraph break or line break to be suppressed into field code so that you end up with {MERGEFIELD Address2 \f "¶
"} or {MERGEFIELD Address2 \b "¶
"} (use a real paragraph break or line break (↵) in place of the '¶');
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
With the addition of the switch, there's no need for an IF test.

Suppress Output on Unused Labels in a Label Merge
Ordinarily, any default text intended for printing on labels will appear on all labels on the last page of a label merge, even after the last record has been output. To prevent that, you can use an IF test that looks at the MERGEREC field’s value. For example":
{IF{MERGEREC}<> "" "Text to Display"}
This can be use with the conditional display of other mergefields in conjunction with the default text. For example:
{IF{MERGEREC}<> "" "Default Text: {IF«MyField»= "" "No Record" «MyField»}"}
or:
{IF{MERGEREC}<> "" "Default Text: {IF{MERGEFIELD MyField}= "" "No Record" {MERGEFIELD MyField}}"}

NOTE WELL: The field brace pairs (i.e. '{ }') for the above examples are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field constructions are all required.