1. I'm working on a more complete version of this message as I feel I've
finally got very near to the bottom of it, but for now...
a. if DDE does not work at all it may be worth checking that it is not
blocked in Excel (if Excel Tools|Options|General|"Ignore other Applications"
is checked, Excel blocks DDE connection attempts).
b. When you format your ZIP column in Excel as Numeric, do any of your
9-digit ZIPs still look like 12345-6789 (i.e. with a hyphen?) If so, let's
call them "Text ZIPs", and everything else "Numeric ZIPs"
2. When you connect using OLE DB (which is the default that WOrd should be
using if you didn't managed to use DDE) then...
a. If Excel thinks that the first 8 ZIPs in your ZIP column are numeric
(either 5 or 9 digits) then
i - "Numeric ZIPs" should get through to Word as numbers without any
leading zeroes
ii - "Text ZIP"s will be lost and converted to 0
b. If there are /any/ "Text ZIPs" in the first 8 cells of your ZIP column,
then
i - all ZIPs should get through to Word more or less as you see them in
Excel.
3. If you apply the IF field we are discussing
a. in situation 2(a), "Numeric ZIPs" should be formatted correctly whether
they are 5 or 9 digit, but "Text ZIPs" will appear as 00000
b. in situation 2(b), then "Numeric ZIPs" should be formatted correctly but
"Text ZIPs" will be treated as if they are a numeric expression to be
calculated (e.g. 12345-6789 = 5556), the IF field will regard them as being
99999, and you will see e.g. 05556
4. You can only fix the 2(a)ii problem by using DDE or by fixing the data
and formatting in Excel (e.g. so that those "Text ZIPs" are all numeric)
5. You can probably fix the 3(b) problem by using a slightly more
complicated set of fields in Word, e.g.
{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }
(e.g. if it's a Text ZIP like 12345-6789 then { QUOTE "{ MERGEFIELD
zip }99999 \# 0 } will be 12345-678999999, i.e. a number less than zero. In
that case you insert the field without change (it only seems to be when Word
is evaluating a condition that it sees the "-" as a numeric operator)
--
Peter Jamieson
http://tips.pjmsn.me.uk
"MrsMac" wrote in message
...
I reformatted the zip field to numbers, then ran the merge with the
switches
previously detailed. The zips that were showing up as four digits in Excel
converted just fine to five digits. But my nine-digit zips changed to five
zeroes. I thought this might be because of the hyphen, but when I remove
the
hyphen and run the merge, I get nine straight numbers.
"Graham Mayor" wrote:
What does a nine digit zip display in the merge without the switch?
The conditional field will only produce the require result if it produces
nine digits
123456789
if it produces
000000000
00000
12345-1234
then it is not going to work as it stands. It will probably work best if
the
column is formatted as simple numbers.
If the data is correctly displayed in Excel then From the Tools menu in
Word, select Options and then go to the General tab and check the box
against the "Confirm conversions at open" item. Then when you attach the
data source to the mail merge main document, you will be given the option
of
using the DDE method of connection which should read the data as you have
it
formatted in the table.
--
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
MrsMac wrote:
Thanks -- it worked, sort of. I get five-digit zips instead of
four-digit ones. But it doesn't merge the nine-digit ones. I get five
zeros instead. Is this because I have the field formatted as Zip Code
instead of Zip Code + Four? When I change the format to Zip + Four,
all my five-digit zips turn into five zeroes, a hyphen, then FOUR
numbers of the five-digit zip code. My database is over 5000 entries
-- too many to go through and change it all by hand -- there's got to
be a better way!
How can something so seemingly simple be so difficult to fix?!
"Peter Jamieson" wrote:
This
{ IF {MERGEFIELD ZIP } 99999" { MERGEFIELD ZIP \# "00000'-'0000"
}" "{ MERGEFIELD ZIP \# "00000" }"}
should be more like this
{ IF {MERGEFIELD ZIP } 99999 "{ MERGEFIELD ZIP \# "00000'-'0000"
}" "{ MERGEFIELD ZIP \# "00000" }"}
Everything else looks OK to me (as ong as /all/ the {} are the sort
you enter with ctrl-F9)) although I would probably lay it out either
as
{ IF { MERGEFIELD ZIP } 99999 "{ MERGEFIELD ZIP \#"00000'-'0000"
}" "{ MERGEFIELD ZIP \# "00000" }" }
or
{ IF { MERGEFIELD ZIP } 99999 "{ MERGEFIELD ZIP \# "00000'-'0000"
}" "{ MERGEFIELD ZIP \# "00000" }" }
primarily becasue Word itself usually has space after { and before }
--
Peter Jamieson
http://tips.pjmsn.me.uk
"MrsMac" wrote in message
...
Office 2003
I've been reading a lot on Graham's site about fixing zip codes,
but using switches and field codes is all new to me and I can't
figure it out. I keep
getting the error message "Error! Missing test condition" when I
toggle back.
I'm trying to get an Excel list that has 5-digit zips and 9-digit
zips to print out properly in a label merge. Here's what I have for
the last line of
merge fields:
{ MERGEFIELD CITY }, {MERGEFIELD STATE } { IF {MERGEFIELD ZIP }
99999" { MERGEFIELD ZIP \# "00000'-'0000" }" "{ MERGEFIELD ZIP \#
"00000" }"}
I'm using Ctrl F9 for the brackets, I've entered the above from
Graham's site, but am not sure about spacing, etc.
Thanks for any help -- Mrsmac