Mail Merge: Merge Field Format (date, time, price...)

Initial document created by Raymond PENTIER

When sending a mailing, sometimes the merged numeric values do not display in the desired format in the final document. This is particularly the case for dates, times; sometimes prices. No matter how much you change the input format, the mailing after merging retains its default format.
MERGEFIELD helps effectively resolve these issues.

• Our video
• Method (by example)
  • Preparation
  • Merging
  • Toggle field codes
  • Format codes
  • Formatting
• Observations
• And OpenOffice/LibreOffice?

Our video

[ccmbg_widget type="video" id="544772" key="1397363885" site="ccm" startMode="2"]

Method (by example)

Preparation

Base document :
On *, I sold * worth of goods, but this morning, between * and *, I lost * items.
Database fields :
Dat, Vent, Déb, Fin, Pert.
After inserting the fields into the base document, we obtain the template letter :
On "Dat", I sold "Vent" worth of goods, but this morning, between "Déb" and "Fin", I lost "Pert" items.
We fill in the database with the values :

25/03/08 1234,56 8:15 11:45 365
12/04 123456,78 8:00 12:50 26,5

Merging

After the merge, the preview offers two letters :
On 25/03/08, I sold 1234,56 worth of goods, but this morning, between 8:15 and 9:45, I lost 365 items.
On 12/04, I sold 123456,78 worth of goods, but this morning, between 8:00 and 12:50, I lost 26,5 items.

Toggle field codes

In the template letter, press [alt]+[F9]; it displays :
On { MERGEFIELD "Dat" }, I sold { MERGEFIELD "Vent" } worth of goods, but this morning, between { MERGEFIELD "Déb" } and { MERGEFIELD "Fin" }, I lost { MERGEFIELD "Pert" } items.

Format codes

To obtain a date/time format ( @ ) with the day in two digits, the month in full, and the year in four digits, add the code

@ "dd MMMM yyyy"

.
To obtain a number format ( # ) with thousand separators, two decimal places, and euro symbol, add the code

# "# ##0,00 €"

.

• Note that you need quotes when you want spaces.

Formatting

Modified template letter :
On

{ MERGEFIELD "Dat" @ "dd MMMM yyyy"}

, I sold

{ MERGEFIELD "Vent" # "# ##0,00 €"}

worth of goods, but this morning,
between

{ MERGEFIELD "Déb" @ "hh 'H' mm"}

and

{ MERGEFIELD "Fin" @ "h 'hours' mm"}

, I lost

{ MERGEFIELD "Pert" # "# ##0,00 €"}

items.

Result after a new [alt]+[F9] :
On March 25, 2008, I sold 1,234.56 € worth of goods, but this morning, between 08 H 15 and 9 hours 45, I lost 365.00 € worth of items.
On April 12, 2013, I sold 123,456.78 € worth of goods, but this morning, between 08 H 00 and 12 hours 50, I lost 26.50 € worth of items.

• Note that the keyboard shortcut [maj]+[F9] only toggles the display of the selected field.

Observations

• Nothing new in this article; only the way of presenting things is a bit different ...
• Your observations, corrections, and additions are welcome!

And OpenOffice/LibreOffice?

The most common case is displaying numbers with 2 decimal places, thanks to walex, and without developing the topic, we can indicate that on the Writer document,

• right-click on the data,
• choose fields,
• choose database,
• select the data,
• check "select by user"
• choose the format with 2 digits after the comma.

Thanks to walex !