View Single Post
  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How to format Zip Code from 9 to 5

Hi Peter,

How about:
{SET Val {MERGEFIELD ZipCode}}{IF{Val} 99999 {SET ID {Val \# "00000'-'0000"}} {SET ID {Val}}}{=-{=-{ID}-ID}/2 \#
0000;;}{=-({ID}*(-1)-ID)/2 \# ;-0000;}
This handles 4-digit, 5-digit, 5+4-digit formatted, 5+4-digit unformatted (ie 9 digit) and the OP's scenario. It works on the
assumptions that no:
.. 5+4-digit zip codes have '0000' as the 4-digit part; and
.. 5-digit zip codes start with 0.

--
Cheers
macropod
[MVP - Microsoft Word]


"Peter Jamieson" wrote in message ...
FWIW previously I had

{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }

which deals with numeric ZIPs up to 9 digits and 12345-6789 text format zips as long as the latter were already correctly
formatted for output, which I think (being text) they typically would be, i.e. it would not deal with 2345-6789 meaning 02345-6789

With a slightly modified test any "-zero" suffix - as mentioned - e.g. -0000, --000 etc. (but not a single trailing "-") can also
be removed and just as an alternative approach to the output you could do it as something like.

{QUOTE{SET Z{MERGEFIELD zip}}{={Z}-{Z}} \#";'{Z}';'{={Z}-99999.5 \#"'{=Z \#00000'-'0000}';'{=Z \#00000}'"}

I guess there could be a simplification in there to split the three cases ("hyphenated", "5 digits or less" and "6-9 digits" using
a single \#.

Either way, I think something like that should deal with the vast majority of these ZIP issues except the case where the ODBC
provider/OLE DB provider has already converted texts to 0 because of mixed types in the Zip column.

Cheers,

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be returned as 75043.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.