Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Word 2003: Mail Merge SQL Expanding

    Hi All,

    I've run into something which MIGHT be a problem although there is an "unacceptable" workaround.

    I have an Excel spreadsheet that serves as a data base for a mail-merge. Each row represents a customer. For discussion here, there are only 3 important cols: name, amount, paid (whether the bill has been paid or not: if yes, cell = "paid", if not, cell is blank).

    I want to generate letters only for those who have not paid their bill. Setting up the mail-merge is easy.

    However, my first thought on how to skip the records for customers who've paid was to use the SKIPIF field. Simple enough. However, in the Help, there is the following "warning":

    You may find the SKIPIF field used in documents created in previous versions of Word, but don't use the SKIPIF field in the current version of Word. You can select data records more easily by using the Mail Merge Recipients dialog box, which appears when you connect to a data source during a mail merge.

    OK. No big deal. I delete the SKIPIF and follow the instructions. When going thru the mail-merge dialog, I go into Edit Recipients. For all of the columns in the Excel spreadsheet, there is a drop down arrow (similar to Excel's filtering) so I click the drop-down for the Paid field. There are a couple of choices: All, paid, blanks, Advanced. Easy enough: pick blanks and no longer see the records with "paid".

    Howevever, if I want to see all records, the All choice is grayed out - can't get back there. So I go into Advanced. That has a QBE (Query By Example) grid with drop-downs for the fields (name, amount, paid), comparison (equal, not equal, etc), and value. There are successive rows, currently blank, if I want to add an "And" or "Or" condition.

    Now by deleting what was created (select "None" for field), I can again see All records while editing Recipients.

    First question: is there some bug that prevents me from selecting All in the Edit Recipients once having made a change?

    But it gets worse: every time I open the mail-merge file and view the QBE for the Paid column, the number of rows (conditions) seems to double. That is, if I selected field=Paid, condition=Is Not Equal, value=paid, I first see 1 row with these values; next time opening the file, I see 2 rows connected with "Or"; next time, 4 rows; 8 rows; etc. When I close the file, I prompted to save changes. What changes? Word's expansion of the # of conditions to check, even though I didn't add them; next time I open the file, I see the "warning" about opening a file that needs a data source and it shows the SQL generated by the "Advanced" option - continually expanding. If I don't save the file, next time opening it, there is the same number of rows in the QBE as the previous opening.

    Second question (series of questions): why is this happening? if I save the file every time (just a habit, even if no changes were made to the mail-merge document), the SQL will continue to grow - is there some limit after which the document will become corrupted or noticeably slow down? While the number of records in this Excel DB is only a few hundred, I would think that having to filter each record on 64 or 128 conditions (all the same) has to slow things down.

    Third question: why the "health warning" about SKIPIF? I understand that creating the proper SKIPIF field in Word might not be as easy as editing recipients and using a QBE grid, but my version of the mail-merge document with SKIPIF works just as well without any potential infinite expansion of SQL.

    And last question: assuming both of the observations on using the Edit Recipients are bugs, have these bugs carried over into subsequent versions of Word? I have 2010 but haven't tested this there. Does SKIPIF still exist in 2010?

    TIA

    Fred
    Last edited by fburg; 2014-11-22 at 12:25.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    In answer to your questions:
    1. There is a bug in Word's mailmerge filtering, particularly when the 'Blanks' option is used. You can access the 'All' option again by clearing the duplicate entries (or all entries) via the 'Advanced' dialogue.
    2. Not a priority for Microsoft
    3. It's not a warning, but advice that there's a (supposedly) easier/better way to do it now.
    4. As of Word 2010, the bug has still not been fixed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Paul.

    1. If I clear the entries (set the field in all conditions to "None"), all records are shown in the Edit Recipients list, so no need to select "All". Oh well.

    2. OK. At the risk of skipping Microsoft's advice, I think I'll stay with SKIPIF rather than risk document corruption with an infinitely expanding SQL.

    3. advice? OK.

    4. Thks for verifying 2010. I don't have 2013 but would assume the problem is there too. If I have some extra time (rarely the case), I'll test the files on a friend's PC with 2013.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •