Hi Peter,
I think you've got too much free time on your hands ...
I agree that adding the extra 0 to preserve leading 0s in the 5-digit part of the Zip Codes is necessary, and I follow what you've
done by dropping the 'SET ID' part of the field coding and substituting Val expressions - it reduces the overal field coding effort
and creates one less bookmark for people to worry about.
I had a look on the USPS website and it seemed from what I read there that a +4 code of 0000 wouldn't occur, but it'd be nice if
someone could confirm this. If they do occur, I can't see how a mailmerge using data like the OP's could differentiate between a
valid 0000 and an invalid one.
--
Cheers
macropod
[MVP - Microsoft Word]
"Peter Jamieson" wrote in message ...
OK, IMO yours is the best approach so far. AFAICS you can modify it ever so slightly to
{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}
which still does everything you say but always does the first five digits as 5 digits and drops the second part of the IF
Then if you think you need to display "-0000" you can do
{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;'-0000'}
or
{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000'-0000';;}{=-({Val}*(-1)-Val)/2 \#;-0000;'-0000'}
depending on the requirement.
/If/ you need also to deal with zips with a trailing "-" but no digits after it, you can either insert an extra test, e.g.
{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val { ={ Val }-0 }}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}
or
{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val {QUOTE "{Val}0"}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}
or nest the 99999 test inside the new IF.
Best regards
Peter Jamieson
http://tips.pjmsn.me.uk
Peter Jamieson wrote:
Hi macropod,
Just as a quick response: it all works as described but...
...no: . 5-digit zip codes start with 0.
this is not the case, at least as far as US ZIPs are concerned (which is why people often have to use the '00000' format with
5-digit ZIPs in Excel). I think that can be easily fixed by changing your 0000 to a 00000, but the lack of 4-digit ZIPs there may
allow further simplification in your code. I don't have a full list of the ZIP rules but found
http://en.wikipedia.org/wiki/ZIP_code useful.
...no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and
I have also made this assumption (which seems to be in line with what the OP is trying to do anyway). The wikipedia article
doesn't explicitly state that -0000 is not used but I hope the USPS managed to avoid it :-)
For an Excel column that supported international postcodes things would have to be rather different anyway. If you tried to put
all types of code in a single column, it would have to be alpha (because of e.g. UK and Canadian codes) and then you probably
would have to deal with 4-digit codes, unless it's OK in countries with 4-digit codes to preface the code by 0 etc. etc. Then you
would have to deal with what the OLE DB provider does to things it thinks are numbers. At which point it's probably simpler from
a mailmerge perspective to suggest that people put the entire address in a single Excel cell...
I'll have another look later anyway.
Peter Jamieson
http://tips.pjmsn.me.uk
macropod wrote:
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.