2

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).

Sadık
  • 4,249
  • 7
  • 53
  • 89
ChrisC27
  • 23
  • 2

1 Answers1

0

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...

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
  • Thanks so much. The alias works and allows the formatting. You are correct that it won’t let me pick an alias that is the same as the field name. I have had to use ‘Annual Budget’ instead of Annual Budget, but I don’t think it is too bad a trade off to have to choose between formatted figures or headers without odd single quote marks! – ChrisC27 Sep 26 '19 at 18:16