Leading zero in zipcode-mergefield in MS Word 2007 is not displaying

vikasbapat
vikasbapat used Ask the Experts™
on
Hello,

I'm using MS Word 2007 document. In the document mergefields are used to insert some address data from database.
Issue is - When I get any 5/9 digit Zipcode starting with a zero, the leading zero is dropped out.

I tried one code like this -
mergefield ZipCode {if {mergefield ZipCode} > "99999" {mergefield ZipCode \#"00000'-'0000'" {mergefield ZipCode \"# "00000"}

Above is working fine for 9 digits zipcode starting with zero, however this not displaying the same code is not working for 5 digits zipcode.
eg., zipcode(02312-1234) with 9 digits is shwon as - 02312-1234
and 5 digits zipcode(02312) is displayed as - 00000-2312

Please reply if there's any good solution for this.
It should work for both 5 and 9 digits zipcode.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What data type is the Zip Code field in your database?

Make sure it is Text and not Number

See also the following link:
http://office.microsoft.com/en-us/excel-help/keep-leading-zeros-in-number-codes-HA010342581.aspx

It starts regarding Excel, but mentions other programs

"For example, suppose you want to use a list of addresses in a workbook as the data source for a Mail Merge operation, and one of the columns is a zip code with a custom format of 00000. In this scenario, Access keeps the leading zeros, but Word removes the leading zeros. In the case of Word, you can specify a calculated column as the postal code field in the Mail Merge operation to ensure that the leading zeros are not removed."
Top Expert 2010

Commented:
If your data source is Excel, and the ZIP/ZIP+4 is entered as a number in a single column, then I would add another column to force it to text using this formula:

=TEXT(A2,"[>99999]00000-0000;00000")

Adjust the A2 as needed.  It forces five digits for entries with just a ZIP, and 00000-0000 for entries entered as a ZIP+4.

Author

Commented:
Hello PaulNSW,

The Zipcode field is in varchar format.
Link provided is for Excel, can it work for Word as well?


Thanks.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
Hello matthewspatrick,
Can we use formats/formulae for Excel in Word as well?

Thanks.

Author

Commented:
Hello PaulNSW,

 I'm checking for solutions for Word on the link-

http://office.microsoft.com/en-us/excel-help/keep-leading-zeros-in-number-codes-HA010342581.aspx


Thanks,

Author

Commented:
Hello PaulNSW,

Below link is not giving solutions for the issue regarding zip code
http://office.microsoft.com/en-us/excel-help/keep-leading-zeros-in-number-codes-HA010342581.aspx


Please reply if there's any good solution for this.
It should work for both 5 and 9 digits zipcode.

Thanks.
Any chance of a copy of your Word doc and DB, or partial - I just tried to recreate on my PC and it worked no problems

You shouldn't need the mergefield formatted the way you mentioned in the first post.  That implies it's stored as a number/integer

Author

Commented:
Hello PaulNSW,

Can you post the mergefield format you have used?
In my application the Zipcode field is stored in nvarchar, but when the doc is merged leading zero is dropped out.

mergefield ZipCode {if {mergefield ZipCode} > "99999" {mergefield ZipCode \#"00000'-'0000'" {mergefield ZipCode \"# "00000"}
Can we format above code to add any function to calculate length of Zipcode?
( like - If len(zipcode)>5, etc. ? )

Thanks.
What is your database stored as?  I just used Access as a DB to test and it doesn't have and explicit varchar field type.

I didn't use any mergefield format with Word.  The field in Access just had Zip codes, "01234" and "012345-01234" and they appeared correctly in word

Author

Commented:
Hello,
Database is in SQL server 2005. There's no issue with field's data type.



Thanks.
I found these regarding using nvarchar fields with Word

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/ab94f41d-6685-4136-95ee-efcd64536a20

http://www.sqlservercentral.com/Forums/Topic938775-391-1.aspx

http://www.wordbanter.com/showthread.php?t=114982

It's for Word 2000, but maybe nothing has changed. Other than that, I'm not sure I can assist further. Sorry!

Author

Commented:
Hello,
Zipcode field is stored as nvarchar in database.When the doc gets merged its gives issue for zipcode.

mergefield ZipCode {if {mergefield ZipCode} > "99999" {mergefield ZipCode \#"00000'-'0000'" {mergefield ZipCode \"# "00000"}

Above is working fine for 9 digits zipcode starting with zero, however this not displaying the same code is not working for 5 digits zipcode.
eg., zipcode(02312-1234) with 9 digits is shwon as - 02312-1234
and 5 digits zipcode(02312) is displayed as - 00000-2312


Please reply if there's any good solution for this.
It should work for both 5 and 9 digits zipcode.

Thanks.
nvchars is just text, not numbers, so cannot be used with > or <

That's like saying, if Cake > Cheese then Dance

I found this
http://www.gmayor.com/formatting_word_fields.htm
Search the page for US Zip codes and it gives the following at the end

"Calculations cannot be performed on text, so in order for the above to be of use, the data should be entered as a continuous number of 5 or 9 digits."

The "above" it refers to is a mergfield formula almost the same as yours

Author

Commented:
Hello,

mergefield ZipCode {if {mergefield ZipCode} > "99999" {mergefield ZipCode \#"00000'-'0000'" {mergefield ZipCode \"# "00000"}

Above is working fine for 9 digits zipcode starting with zero, however this not displaying the same code is not working for 5 digits zipcode.
eg., zipcode(02312-1234) with 9 digits is shwon as - 02312-1234
and 5 digits zipcode(02312) is displayed as - 00000-2312

Can I get solution for above?

Thanks.
Press Alt and F9 to enter Field code mode

Press Ctrl and F9 every time you need to enter the brackets.  You cannot enter this code using, Right click -> Edit Field, it must be entered directly into the Word Doc

mergefield
This will only work if the zip code is stored as a continuous string, eg 01234 or 012345678.

Author

Commented:
Hello PaulNSW, I hadused above code already.. and i'm using same code as above using Atl/Ctrl f9 while puting above code.
But its not working..
I guess the else condition is not executing. I got this o/p when I merged doc -
(with Alt F9)  
{IF 04567 > 99999 00000-4567  04567}, here the Else part is correct, after merging its showing - 4567 only.
Why its working fine for 9 digits and not for 5 digits? Have you tried above in the doc?

Thanks.
Not sure why its cutting out the first 0.

I was using the above code (its a screenshot from the work document I created to test)
With an AccessDB with the field type set as Text/Character

Are you sure you have all the brackets, quotes and double quotes in the right places?

Author

Commented:
Yes, brackets,quotes, etc. are used in proper format..
While I tried different combinations for the above code..
The finalized code worked for 9 digits is posted in my query..


Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial