Search code examples
ms-wordmailmerge

Mail merge: How can I format indiviidual fields within "database" field

For a mail merge in Microsoft Word from data in Microsoft Excel, I have written a DATABASE field that successfully adds all of the fields I want and dynamically changes for each mail merge record ("many to one").

I have then tried to format the numbers within this DATABASE statement, but when I used FORMAT() the number did change to the format I wanted, however the header was replaced with Expr1003.

Is there a way to format the numbers within the DATABASE statement shown below but without losing the header titles?

Code without formatting:

{DATABASE \d"{FILENAME \p}/../data5.xlsx" \s "SELECT [Accountable Officer], [Cost Centre Group], [Description], [Annual Budget], [Outturn Forecast], [Outturn Forecast Variance] FROM [data$] WHERE [Accountable Officer] = {Quote 39}{MERGEFIELD Accountable_Officer}{Quote 39} ORDER BY [Cost Centre Group] "\l \b "16" \h}

If I amend Annual Budget with FORMAT() as below:

{DATABASE \d"{FILENAME \p}/../data5.xlsx" \s "SELECT [Accountable Officer], [Cost Centre Group], [Description], FORMAT([Annual Budget], '£#,##0;-£#,##0'), [Outturn Forecast], [Outturn Forecast Variance] FROM [data$] WHERE [Accountable Officer] = {Quote 39}{MERGEFIELD Accountable_Officer}{Quote 39} ORDER BY [Cost Centre Group] "\l \b "16" \h}

then the figures shown in the Annual Budget column are formatted correctly, but the header title then changes to Expr1003 (or Expr1004, Expr1006 etc).

Solution

  • The reason for the change in the field name is because the function is "hiding" the fied name. In this case, the Database field is creating an expression for the field name.

    One way around this is to pre-format in the data source, in the case of Excel, by turning the cell content into text instead of a number.

    The other way is to assign an alias as the field name using AS:

    FORMAT([Annual Budget], '£#,##0;-£#,##0') AS Budget.

    I think you should be able to use AS [Annual Budget], but test this, first, to be sure the basic syntax works. My quick test in Access did not allow using the same field name as the alias, but my test in Word did allow it...