[MrsMac]

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

[Peter Jamieson]

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

[MrsMac]

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

[Graham Mayor]

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

[MrsMac]

Without the switch, the nine-digit zip displays one zero in the zip field
when merged. I have either five-digit zips or nine-digit zips separated by a
hyphen.
When I reformat the field from Zip to Numbers, any zips starting with zero
become four digits.

I have tried the "Confirm conversions at open" alternative; I get an error
message saying "Word cannot re-establish a DDE connection to Microsoft Excel
...."

"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

[MrsMac]

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

[Peter Jamieson]

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

[Peter Jamieson]

How can something so seemingly simple be so difficult to fix?!

I certainly wish it were simpler, but the "how" boils down to "different
software has different target groups, has been designed and implemented by
different people at different times, sometimes with no good standards to
work to"

I've posted a shortened version of this message in reply to your most recent
posts...

However, you may find the following helpful (and Graham - if you think it is
worth incorporating any of this in your web pages, please do so: I'll
probably also put it into my t0003 page about Excel-related problems.
However, the following results from a fairly quick look, not years of
experience)

Generally speaking, switching to the DDE approach that Graham has mentioned
will sort out a lot of problems using Excel data sources. It doesn't work in
all cases (e.g. if you do not actually have Excel on your system, the data
is not in the first sheet of the workbook, or you need to use "unusual"
Unicode characters) but in many cases there is a simple way of dealing with
that (e.g. if the sheet isn't the first in the book, copy the workbook and
delete all the sheets before the one you want).

1. In Excel, you can enter, store and format, ZIP codes in a number of
different ways, including:

a. If you enter 5-digit ZIPs by entering the 5 digits, Excel will save the
data as a number with "General" formatting and display the number, right
justified, with no leading zeroes. If it is possible to have ZIP codes with
leading zeroes, you can ensure that you always see all 5 digits by applying
the ZIP format, which is a numeric format: 00000.

b. If you enter 9-digit ZIPs by entering the 9 digits without the hyphen,
Excel will save the data as a number with "General" formatting and display
the number, left justified, with no leading zeroes. If you want to display
them with the hyphen, you can apply the ZIP+4 format which is a numeric
format: 00000\-0000 (i.e. applying the format does not change the stored
number into text)

c. If you enter 9-digit ZIPs by entering 5 digits, a hyphen, then 4 digits,
Excel will save the data as text with "General" formatting and display what
you entered, right justified.

d. If you format your cells as text, what happens depends on when and how
you do it:
i) Once you have entered a number in a cell that is not formatted as
text, Excel stores it as a number, even if you apply Text formatting to the
cell.
ii) If you format a cell as text, /then/ enter a number, Excel stores the
number as text (actually, I am by no means sure that this is always the
case). But it also flags a warning using one of its red(?) corner markers.
iii) If you already have numbers in cells in a column and you want to turn
them into text format, you can do it, but not by applying text formatting -
you can
- select the column
- select the Data|Text to Columns... menu option
- click Next through the wizard until you reach Step 3 of 3, then
select Text as the Column Data Format.

As you can imagine, it is not all that easy to tell the difference between
the different things merely by looking at them. Typically, the 9-digit
number in (b) will be right-aligned even when it is displayed with the "-",
and the text in (c) will be left-aligned, as long as you have not explicitly
aligned the result.

Also, applying either of the ZIP or ZIP+4 formats to the entire column is
not much help because
e. if you apply the ZIP format to 9-digit ZIPs, you just see a 9-digit
number with no hyphen
f. if you apply the ZIP+4 format to 5-digit ZIPs, the ZIP12345 will appear
as 00001-2345. Pretty useless, in fact.

2. When Word gets the data from Excel,
a. DDE will retrieve the data /as you see it/ It doesn't matter whether you
see 12345-6789 because you entered 12345-6789 (as in (c) or applied a ZIP+4
format to 123456789 (as in (b)).
b. the default method Word 2002 and later use to get data from Excel (an
"OLE DB provider") gets the data /as it is stored/, but with a twist. i.e.,
if you entered 123456789, Word will retrieve 123456789 whether or not it is
formatted as a ZIP+4. If you entered 12345-6789, Word will retrieve
"12345-6789".

The twist is that when it gets the data, the OLE DB provider tries to assign
a data type to the entire column. In other words, it sees the whole column
as numeric, or as text. Roughly speaking,
c. if you entered all your ZIPs as numbers and applied ZIP and ZIP+4
formatting, the provider will decide that the column is numeric
d. if all your ZIPs are 9-digit ZIPs that you entered as per 1(c), the
provider will decide that the column is text
e. if there is a mix of types 1(a), 1(b) and 1(c), the provider will decide
on the contents of the first 8 cells in the ZIP code column. If they are
/all/ numbers (types 1(a) and 1(b), the provider will decide that the whole
column is numeric. If /any/ of them are texts, the provider will decide that
the whole column is text.

Then the provider has to decide what to do about numeric values in a text
columne, and text values in a numeric column
f. If the provider decides that the column is numeric, and there are no
type 1(c) ZIPs, then we are OK in the sense that all the ZIP data gets
through to Word as 5- or 9-digit numbers
g. If the provider decides that the column is text, we are OK because the
provider then appears to use the display text for all the cells. As long as
they all look like 5 or 9-digit ZIPs, that's what Word will see
i. However, if the provider decides that the column is numeric, any 1(c)
type ZIPs will be lost as they are passed to Word as the numeric value 0.

3. If you got this far, it is perhaps worth asking
a. How could we avoid situation 2(i)?
b. If we manage to avoid situation 2(i), how can we display all the ZIP
codes in Word correctly?

4. I /think/ you can fix 3(b) using the fields I suggested elsewhere, i.e.

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

There could well be a simpler formulation but at the moment I am only trying
to think of /a/ way to do it.

5. As for 3(a), it might be better to ask an Excel expert (although I am not
convinced that they have to cope with this either unless they want to use
their data in a merge). A lot depends on how you are acquiring your data (is
it in spreadsheets prepared elsewhere, or by you?), whether you prefer
trying to attain consistency during data entry or whether you prefer to
post-process any column that contains ZIPs, and whether you prefer
enforcement (which probably requires a lot of code that you could do
without) or encouragement to conform to standards.

Because of the problem that the OLE DB provider can have with mixed data
types, I think that any solution should aim to end up with an Excel ZIP
column having either /all numeric/ ZIPs or /all text/ ZIPs. Once the data
has been entered and there is potentially a mix of numeric and text data, I
think the only way to achieve this is to use approach 1(d)iii above - i.e.
change the entire column to text. 5-digit numeric ZIPs will become 5-digit
texts, 9-digit numeric ZIPs formatted as ZIP+4 will become 9-digit texts,
and 5-4 text ZIPs will remain as 5-4 texts. You would need the complex Word
field code in (4) to deal with that column correctly.

If you prefer to stick to numeric codes, a good approach might be to try to
encourage people to enter 5 digits or 9 digits and avoid entering 5-4 texts.
There are a couple of ways you could consider doing that, e.g.
a. Select the ZIP column, go to Format|Cells|Number, select Custom, and
enter something like the following (or select it if it is already there):
[100000]00000;[99999]00000-0000;[Red]"wrong" @
b. Format the column header as General or Text

Numeric 5 and 9-digit ZIP codes should appear correctly, and any Text ZIPs
should appear in Red with "wrong " in front. Or...
c. use Excel conditional formatting (which lets you apply colour etc. to
the cell).

Or I suppose you could decide to enter all ZIPs in two columns - 5 digits in
the first, and 4 digits in the second, blank if it's a 5-digit zip. Then
piece the ZIP code back together in Word, e.g.

{ MERGEFIELD zip5 \#00000 }{ MERGEFIELD zip4 \#"'-'0000" }

And so on...


--
Peter Jamieson
http://tips.pjmsn.me.uk

"MrsMac" wrote in message
...
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

[Graham Mayor]

Peter
I have copied the message and will see how this useful information can be
incorporated into the web page, but it will have to wait until the New Year


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Peter Jamieson wrote:
How can something so seemingly simple be so difficult to fix?!

I certainly wish it were simpler, but the "how" boils down to
"different software has different target groups, has been designed
and implemented by different people at different times, sometimes
with no good standards to work to"

I've posted a shortened version of this message in reply to your most
recent posts...

However, you may find the following helpful (and Graham - if you
think it is worth incorporating any of this in your web pages, please
do so: I'll probably also put it into my t0003 page about
Excel-related problems. However, the following results from a fairly
quick look, not years of experience)

Generally speaking, switching to the DDE approach that Graham has
mentioned will sort out a lot of problems using Excel data sources.
It doesn't work in all cases (e.g. if you do not actually have Excel
on your system, the data is not in the first sheet of the workbook,
or you need to use "unusual" Unicode characters) but in many cases
there is a simple way of dealing with that (e.g. if the sheet isn't
the first in the book, copy the workbook and delete all the sheets
before the one you want).
1. In Excel, you can enter, store and format, ZIP codes in a number of
different ways, including:

a. If you enter 5-digit ZIPs by entering the 5 digits, Excel will
save the data as a number with "General" formatting and display the
number, right justified, with no leading zeroes. If it is possible to
have ZIP codes with leading zeroes, you can ensure that you always
see all 5 digits by applying the ZIP format, which is a numeric
format: 00000.
b. If you enter 9-digit ZIPs by entering the 9 digits without the
hyphen, Excel will save the data as a number with "General"
formatting and display the number, left justified, with no leading
zeroes. If you want to display them with the hyphen, you can apply
the ZIP+4 format which is a numeric format: 00000\-0000 (i.e.
applying the format does not change the stored number into text)

c. If you enter 9-digit ZIPs by entering 5 digits, a hyphen, then 4
digits, Excel will save the data as text with "General" formatting
and display what you entered, right justified.

d. If you format your cells as text, what happens depends on when and
how you do it:
i) Once you have entered a number in a cell that is not formatted
as text, Excel stores it as a number, even if you apply Text
formatting to the cell.
ii) If you format a cell as text, /then/ enter a number, Excel
stores the number as text (actually, I am by no means sure that this
is always the case). But it also flags a warning using one of its
red(?) corner markers. iii) If you already have numbers in cells in
a column and you want to turn them into text format, you can do it,
but not by applying text formatting - you can
- select the column
- select the Data|Text to Columns... menu option
- click Next through the wizard until you reach Step 3 of 3,
then select Text as the Column Data Format.

As you can imagine, it is not all that easy to tell the difference
between the different things merely by looking at them. Typically,
the 9-digit number in (b) will be right-aligned even when it is
displayed with the "-", and the text in (c) will be left-aligned, as
long as you have not explicitly aligned the result.

Also, applying either of the ZIP or ZIP+4 formats to the entire
column is not much help because
e. if you apply the ZIP format to 9-digit ZIPs, you just see a 9-digit
number with no hyphen
f. if you apply the ZIP+4 format to 5-digit ZIPs, the ZIP12345 will
appear as 00001-2345. Pretty useless, in fact.

2. When Word gets the data from Excel,
a. DDE will retrieve the data /as you see it/ It doesn't matter
whether you see 12345-6789 because you entered 12345-6789 (as in (c)
or applied a ZIP+4 format to 123456789 (as in (b)).
b. the default method Word 2002 and later use to get data from Excel
(an "OLE DB provider") gets the data /as it is stored/, but with a
twist. i.e., if you entered 123456789, Word will retrieve 123456789
whether or not it is formatted as a ZIP+4. If you entered 12345-6789,
Word will retrieve "12345-6789".

The twist is that when it gets the data, the OLE DB provider tries to
assign a data type to the entire column. In other words, it sees the
whole column as numeric, or as text. Roughly speaking,
c. if you entered all your ZIPs as numbers and applied ZIP and ZIP+4
formatting, the provider will decide that the column is numeric
d. if all your ZIPs are 9-digit ZIPs that you entered as per 1(c), the
provider will decide that the column is text
e. if there is a mix of types 1(a), 1(b) and 1(c), the provider will
decide on the contents of the first 8 cells in the ZIP code column.
If they are /all/ numbers (types 1(a) and 1(b), the provider will
decide that the whole column is numeric. If /any/ of them are texts,
the provider will decide that the whole column is text.

Then the provider has to decide what to do about numeric values in a
text columne, and text values in a numeric column
f. If the provider decides that the column is numeric, and there are
no type 1(c) ZIPs, then we are OK in the sense that all the ZIP data
gets through to Word as 5- or 9-digit numbers
g. If the provider decides that the column is text, we are OK because
the provider then appears to use the display text for all the cells.
As long as they all look like 5 or 9-digit ZIPs, that's what Word
will see i. However, if the provider decides that the column is numeric,
any
1(c) type ZIPs will be lost as they are passed to Word as the numeric
value 0.
3. If you got this far, it is perhaps worth asking
a. How could we avoid situation 2(i)?
b. If we manage to avoid situation 2(i), how can we display all the
ZIP codes in Word correctly?

4. I /think/ you can fix 3(b) using the fields I suggested elsewhere,
i.e.
{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }

There could well be a simpler formulation but at the moment I am only
trying to think of /a/ way to do it.

5. As for 3(a), it might be better to ask an Excel expert (although I
am not convinced that they have to cope with this either unless they
want to use their data in a merge). A lot depends on how you are
acquiring your data (is it in spreadsheets prepared elsewhere, or by
you?), whether you prefer trying to attain consistency during data
entry or whether you prefer to post-process any column that contains
ZIPs, and whether you prefer enforcement (which probably requires a
lot of code that you could do without) or encouragement to conform to
standards.
Because of the problem that the OLE DB provider can have with mixed
data types, I think that any solution should aim to end up with an
Excel ZIP column having either /all numeric/ ZIPs or /all text/ ZIPs.
Once the data has been entered and there is potentially a mix of
numeric and text data, I think the only way to achieve this is to use
approach 1(d)iii above - i.e. change the entire column to text.
5-digit numeric ZIPs will become 5-digit texts, 9-digit numeric ZIPs
formatted as ZIP+4 will become 9-digit texts, and 5-4 text ZIPs will
remain as 5-4 texts. You would need the complex Word field code in
(4) to deal with that column correctly.
If you prefer to stick to numeric codes, a good approach might be to
try to encourage people to enter 5 digits or 9 digits and avoid
entering 5-4 texts. There are a couple of ways you could consider
doing that, e.g. a. Select the ZIP column, go to Format|Cells|Number,
select Custom,
and enter something like the following (or select it if it is already
there): [100000]00000;[99999]00000-0000;[Red]"wrong" @
b. Format the column header as General or Text

Numeric 5 and 9-digit ZIP codes should appear correctly, and any Text
ZIPs should appear in Red with "wrong " in front. Or...
c. use Excel conditional formatting (which lets you apply colour etc.
to the cell).

Or I suppose you could decide to enter all ZIPs in two columns - 5
digits in the first, and 4 digits in the second, blank if it's a
5-digit zip. Then piece the ZIP code back together in Word, e.g.

{ MERGEFIELD zip5 \#00000 }{ MERGEFIELD zip4 \#"'-'0000" }

And so on...



"MrsMac" wrote in message
...
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