Share via

Add mail merge variable

Anonymous
2014-12-10T15:38:42+00:00

I have developed an Excel spread sheet with a mail merge add-on. It tracks yearly church contributions and then provides a contributions summary for tax purposes that are sent out in January. The summary letter has the sentence “Your total contributions for the year 2014 is”

 I wish to make this year to automatically update to the next year i.e. Last year’s letter said 2013. I had a popup that reminded them to change the year in the letter before running the mail merge. One person forgot and caused all kinds of problems for someone preparing the tax form.

In other words, I need a variable that always shows the preceding year date in the mail merge letter

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Suzanne S Barnhill 278.1K Reputation points MVP Volunteer Moderator
2014-12-11T13:32:45+00:00

Click in the field and press Shift+F9. This assumes that you inserted the field correctly using Ctrl+F9 to insert the field brace pairs.

Was this answer helpful?

0 comments

Answer accepted by question author

Paul Edstein 82,861 Reputation points Volunteer Moderator
2014-12-10T23:40:25+00:00

Simple - use a formula field coded as:

{QUOTE{={DATE @ YYYY}-1}}

Note: The field brace pairs (i.e. '{ }') for the above example 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.

Was this answer helpful?

0 comments

19 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Suzanne S Barnhill 278.1K Reputation points MVP Volunteer Moderator
    2014-12-15T14:42:48+00:00

    All of the braces have to be inserted with Ctrl+F9. That means, if you want to type before inserting the fields, that you have to type DATE @ YYYY, select it and press Ctrl+F9, then add the = and -1 and select the entire string and press Ctrl+F9, then add QUOTE and select the entire string and press Ctrl+F9.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-12-15T14:27:00+00:00

    This is my original sentence. 

    "Your total contribution to X Church for the calendar year  was $130.00."

    I them copied the formula sans the braces

    "Your total contribution to X Church for the calendar year QUOTE{={DATE @ YYYY}-1} was $130.00."

    I then hi-lighted the formula and pressed Ctrl+F9 to insert the braces.

    "Your total contribution to X Church for the calendar year {QUOTE{={DATE @ YYYY}-1}} was $130.00. "

    I thought the field should have a darker background when I Click "Highlight Merged Fields. I don't.

    When I hi-light the formula and press Shift+F9 the text disappears.

    What am I doing wrong??

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-12-11T13:19:25+00:00

    I must be doing something wrong. The year date doesn't show up just the formula. The $550.00 is an Insert Merge Field

    Your total contribution to x Church for the calendar year  

    {QUOTE{={DATE @ YYYY}-1}} was $ $555.00.00

    Was this answer helpful?

    0 comments