Link to home
Start Free TrialLog in
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.
ASKER CERTIFIED SOLUTION
Avatar of PaulNSW
PaulNSW
Flag of Norway image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of vikasbapat
vikasbapat

ASKER

Hello PaulNSW,

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


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

Thanks.
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,
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
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
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!
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
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

User generated image
This will only work if the zip code is stored as a continuous string, eg 01234 or 012345678.
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?
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.