Avatar of vikasbapat
vikasbapat
 asked on

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

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.
Microsoft Word

Avatar of undefined
Last Comment
vikasbapat

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PaulNSW

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

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.
vikasbapat

ASKER
Hello PaulNSW,

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


Thanks.
vikasbapat

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

Thanks.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
vikasbapat

ASKER
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,
vikasbapat

ASKER
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.
PaulNSW

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
vikasbapat

ASKER
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.
PaulNSW

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
vikasbapat

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



Thanks.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PaulNSW

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!
vikasbapat

ASKER
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.
PaulNSW

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
vikasbapat

ASKER
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.
PaulNSW

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.
vikasbapat

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
PaulNSW

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?
vikasbapat

ASKER
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.