Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    North Carolina, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge problem: 9 digit zip codes (2003)

    Has anyone encountered an issue where nine digit zip codes in an Excel spreadsheet appear as "0" in the Mail merge, when 5 digit zip codes in the same field appear correct?

    Attached is a sample spreadsheet to demonstrate the problem. Set up a mail merge document with this file as the data source.

    I've tried formatting the zip code column as text but that hasn't solved the problem.
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    North Carolina, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Wierd -- after posting, I tried the same file in Word 2002. In that version, the 9 digit zip codes appear OK, the 5 digit zip codes are blank.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Try the following in Word:
    - Select Tools | Options...
    - Activate the General tab.
    - Tick the check box "Confirm conversion at Open".
    - Click OK.

    Now connect to the Excel workbook again. You'll be asked which method you want to use. Select OLE DB or DDE. Either processes both types of zip code correctly, on my PC at least.

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

    Re: Mail merge problem: 9 digit zip codes (2003)

    I always marvel at how you can put together these field "sequences". I have enough trouble with a simple {IF}.

    However, I'm confused by your zip code formatting. Do you need another 0 in
    >{IF{MERGEFIELD ZipCode}< 10000

    so that a zip code of say 98172 would be less than 100,000? I realize that Word will be ok if the original has a non-zero leading digit but I would not think you want a 5-digit zip like this one to be formatted as 98172-0000, if I follow correctly.

    Similarly in your "complete independence" fields:
    >{IF{Zip1}< 10^5

    Wouldn't that need to be 10^6?

    Fred

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    North Carolina, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Thanks, Macropod.

    I tried what you suggest and I can't get it to work. Word says "Invalid op code in conditional". And I was using CTRL-F9 to insert the field codes. I'm wondering if since the Excel column is formatted as text, maybe the values of these cells is always zero or null?

    I think the work around I later found is better than mastering IF statements in merge field codes. I copied the Excel chart into Word, making it a Word table, and used that for my mail merge source.

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    North Carolina, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    HansV

    OLE DB didn't work for me, but DDE did. DDE is annoying though, since it actually starts up Excel and I had to wait for Excel to open the file and communicate with Word.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    I see you already have a workaround. For me, both OLE DB and DDE worked with the spreadsheet you attached, whereas the conversion option and ODBC failed, one on the 5 digit zip codes and the other on the 9 digit zip codes (I forget which was which).

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Hi LewisFan,

    The error message probably resulted from the field not being input exactly as required. Attached is a document with a demonstration of the field, using an ASK field to generate the 'ZipCode' value, which the output field then processes. You can use this to test the field with various postcodes and formats. You should find that it works equally well for 9-digit codes, with or without a hyphen in the middles, and for 5-digit codes.

    To use the output field in your mailmerge document, simply copy it over, expand the field coding via Shift-F9, and replace the 'REF ZipCode' string with 'MAILMERGE and the name of the field in your source data.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Hi LewisFan,

    You can correct this in your Word merge document, by testing the mailmerge zipcode field with an IF field and using picture switches to format the result. To do this you would code the field as:
    {IF{MERGEFIELD ZipCode}< 100000 {MERGEFIELD ZipCode # 00000} {MERGEFIELD ZipCode # "00000'-'0000"}}
    where 'ZipCode' is your mergefield's name.

    This approach makes the Word merge document independent of the cell format in Excel.

    For complete independence from the Excel data format, you would code the field like:
    {QUOTE
    {SET Zip {Megefield ZipCode}}
    {SET Zip1{=-{=-{Zip}-Zip}/2}}
    {SET Zip2{=-({Zip1}+{=-({Zip}*(-1)-{Zip})/2}-{Zip})}}
    {IF{Zip1}< 100000 {Zip1 # 00000} {Zip1 # "00000'-'0000"}}
    {IF{={Zip1}+{Zip2}}= 0 "" "-{Zip2 # 0000}"}}

    Cheers
    PS: The field braces are created in pairs via Ctrl-F9 - you can't just type them in.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail merge problem: 9 digit zip codes (2003)

    Hi fred,

    The value you queried should be 100000 in both cases. Although 10^5 should evaluate to 100000, it didn't seem to be working properly. I'll correct the original post. See also my latest reply to LewisFan.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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