none
Mail Merge Issue RRS feed

  • Question

  • Hello All,

    I have done a lot of browsing trying to solve this issue. 

    We run a mail merge and the account number prints on letters such as: 0011 123456 7. What is desired is to have just the "3456" print out. I have tried entering: {MERGEFIELD Account_Number \#00000000000} to just try and get rid of the spaces so then a MOD could be applied as well. When I test this the number that is then populated does not even match the account number. 

    I've tried other ways:

    {=MOD({={MERGEFIELD MyData}-1},10000)+1}

    {=MOD({MERGEFIELD MyData},10000)}

    which have resulted in !Missing Operator or Syntax Error

    Does anyone know of a solution? Thank you for your assistance.

    I do not have access to altering the data either.


    • Edited by Chafire Tuesday, January 16, 2018 4:37 PM
    Tuesday, January 16, 2018 4:30 PM

All replies

  • What's the data source? A Word table? An Excel file? A database?

    I suspect you're not going to be able to do this with field codes, you might have to work with the source data...


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Tuesday, January 16, 2018 4:38 PM
    Moderator
  • Hi Cindy,

    Thank you. It is from a database. I have just noticed that when I am attempting this:

    "I have tried entering: {MERGEFIELD Account_Number \#00000000000} to just try and get rid of the spaces so then a MOD could be applied as well. When I test this the number that is then populated does not even match the account number. "

    The number that is being generated is the sum of the first four digits and the very last one soo 0011 123456 7 becomes 00000012363 (0011+7=18+45=63).

    Anyone have an idea as to why it would be adding these numbers together? possibly due to the spacing?


    • Edited by Chafire Tuesday, January 16, 2018 6:05 PM
    Tuesday, January 16, 2018 5:05 PM
  • Depending on what kind of database, and whether you (or someone willing to help you) have access to creating queries, and whether it supports a versatile query language, you should be able to set up a query to pre-manipulate this information so that you can pick it up as a ready-to-go mergefield.

    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    • Proposed as answer by Terry Xu - MSFT Wednesday, January 17, 2018 9:01 AM
    Tuesday, January 16, 2018 6:43 PM
    Moderator
  • The problem you're having is caused by the spaces in the data, so it can't be tested as a number. To parse such data you'll need to test each character individually, along the lines of:

    {QUOTE{SET ID {MERGEFIELD Account_Number}}
    {SET A "{IF{REF ID}= "*0?????" 0}{IF{REF ID}= "*1?????" 1}{IF{REF ID}= "*2?????" 2}{IF{REF ID}= "*3?????" 3}{IF{REF ID}= "*4?????" 4}{IF{REF ID}= "*5?????" 5}{IF{REF ID}= "*6?????" 6}{IF{REF ID}= "*7?????" 7}{IF{REF ID}= "*8?????" 8}{IF{REF ID}= "*9?????" 9}"}
    {SET B "{IF{REF ID}= "*0????" 0}{IF{REF ID}= "*1????" 1}{IF{REF ID}= "*2????" 2}{IF{REF ID}= "*3????" 3}{IF{REF ID}= "*4????" 4}{IF{REF ID}= "*5????" 5}{IF{REF ID}= "*6????" 6}{IF{REF ID}= "*7????" 7}{IF{REF ID}= "*8????" 8}{IF{REF ID}= "*9????" 9}"}
    {SET C "{IF{REF ID}= "*0???" 0}{IF{REF ID}= "*1???" 1}{IF{REF ID}= "*2???" 2}{IF{REF ID}= "*3???" 3}{IF{REF ID}= "*4???" 4}{IF{REF ID}= "*5???" 5}{IF{REF ID}= "*6???" 6}{IF{REF ID}= "*7???" 7}{IF{REF ID}= "*8???" 8}{IF{REF ID}= "*9???" 9}"}
    {SET D "{IF{REF ID}= "*0??" 0}{IF{REF ID}= "*1??" 1}{IF{REF ID}= "*2??" 2}{IF{REF ID}= "*3??" 3}{IF{REF ID}= "*4??" 4}{IF{REF ID}= "*5??" 5}{IF{REF ID}= "*6??" 6}{IF{REF ID}= "*7??" 7}{IF{REF ID}= "*8??" 8}{IF{REF ID}= "*9??" 9}"}
    "{A}{B}{C}{D}"}

    Basically, the above field code uses the * wildcard to ignore everything before the number you want to test and a series of ? wildcards to specify how many characters to ignore after the one you want to test. For each position, there are 10 tests.

    For a macro to convert the above to a working field code, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks thread at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, January 16, 2018 11:08 PM
  • Even if you cannot change anything in the database, in some cases you may be able to issue your own query from VBA when you open the data source using the  MailMerge.OpenDataSource method. For example, with a Jet/ACE data source such as Access, Excel, or even some delimited text files, you may be able to use

    replace(Account_Number,' ','')

    to remove the spaces and

    left(right(replace(Account_Number,' ',''),5),4)

    to get the digits you want.

    However, even if you cannot do that, as long as you are using a recent enough version of Windows Word (this won't work with Mac Word) you may be able to use a DATABASE field within your document to extract the digits, regardless of the data source, as long as

     a. your Account_Number field only contains digits and spaces
     b. you will not have any difficulty installing a small text file along with the .docx

    In this case, the fields to remove the spaces would be something like 

    { QUOTE { DATABASE \d "the full path of the small text file" \s "SELECT replace('{ MERGEFIELD Account_Number }',' ','')" } }

    and the fields to extract the 4 digits would be something like

    { QUOTE { DATABASE \d "the full path of the small text file" \s "left(right(SELECT replace('{ MERGEFIELD Account_Number }',' ',''),5),4)" } }

    I have posted a small sample at https://goo.gl/SQNDTf which should allow you to see how it works. - download the .zip and unzip it if necessary, then read the readme.txt.

    Because Word unnecessarily prevents you from putting a DATABASE field inside a table, if the result needs to be inside a table, you have to set a bookmark to the result of the database field somewhere outside the table first, e.g.

    { SET digits4 "{ QUOTE { DATABASE \d "the full path of the small text file" \s "left(right(SELECT replace('{ MERGEFIELD Account_Number }',' ',''),5),4)" } }" }

    then use { digits4 } at the point where you need the result. So this does not work well for merges to labels.


    Peter Jamieson


    Wednesday, January 17, 2018 9:42 AM
  • Hello Chafire,

    What the statue of your issue? Could anyone's suggestion work for you? If it has, please mark helpful reply to help people who runs into the same issue could find the solution efficiently. If not, please follow up to tell us your testing result and the current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 5:39 AM