My original data source is Excel
I have two fields causing me problems - Phone & Date of Birth
Each field is numeric and formatted as Special Phone Number and Date
I only have about 1 out of 10 records with data
I am using mail merge to complete application forms using the data in Excel
I used switches on the phone field to make the data display properly
My problems a
When I view the records in mail merge if there was no date for a record in
Excel, I see a time displayed 12:00:00 AM. When I view the record where
there is no phone number, I see my formatting instead (000) 000-0000.
How do I tell the mail merge to skip blank cells in Excel so I see nothing?
I prefer to not change my data source (say to text to fix this) in Excel as
it is a "live" spreadsheet that users are continously adding data to to fill
in the missing records for phone number and DOB.

You could use conditionals field eg
{IF {Mergefield Phone} "(000) 000-0000" "{Mergefield Phone}"}
and
{IF {Mergefield DOB} "12:00:00 AM" "{Mergefield DOB}"}

or probably simpler, see the Excel data section of
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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





Jim wrote:
My original data source is Excel
I have two fields causing me problems - Phone & Date of Birth
Each field is numeric and formatted as Special Phone Number and Date
I only have about 1 out of 10 records with data
I am using mail merge to complete application forms using the data in
Excel I used switches on the phone field to make the data display
properly
My problems a
When I view the records in mail merge if there was no date for a
record in Excel, I see a time displayed 12:00:00 AM. When I view the
record where there is no phone number, I see my formatting instead
(000) 000-0000.
How do I tell the mail merge to skip blank cells in Excel so I see
nothing? I prefer to not change my data source (say to text to fix
this) in Excel as it is a "live" spreadsheet that users are
continously adding data to to fill in the missing records for phone
number and DOB.

Graham,

Thanks for the suggestions. I understand how each method appraches the
problem, but I have a few issues.

Going the DDE route, everything seems to work fine until trying to view the
merged document, Word crashes. My versions of Word and Excel are both 2002.
My data set in Excel is 58,000 records. Is one of these the reason?

Using the conditional method, I must not be entering it correctly because I
get the result of PHONE when viewing the merged data in Word instead of a
phone number or nil.

I am entering the merge field (called PHONE H PRE TEXT), highlighting it,
right clicking, choosing Edit Field under Choose A Field and picking IF, when
the Advanced Field Properties text box comes up begining with IF I then enter
the expression as you intruscted for my data:
{IF {MERGEFIELD PHONE H PRE TEXT} "(000) 000-0000" "{MERGEFIELD PHONE H
PRE TEXT}" }

What am I doing wrong?
Do you have step by step instructions for using conditionals?

Thank you,
Jim

"Graham Mayor" wrote:

You could use conditionals field eg
{IF {Mergefield Phone} "(000) 000-0000" "{Mergefield Phone}"}
and
{IF {Mergefield DOB} "12:00:00 AM" "{Mergefield DOB}"}

or probably simpler, see the Excel data section of
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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





Jim wrote:
My original data source is Excel
I have two fields causing me problems - Phone & Date of Birth
Each field is numeric and formatted as Special Phone Number and Date
I only have about 1 out of 10 records with data
I am using mail merge to complete application forms using the data in
Excel I used switches on the phone field to make the data display
properly
My problems a
When I view the records in mail merge if there was no date for a
record in Excel, I see a time displayed 12:00:00 AM. When I view the
record where there is no phone number, I see my formatting instead
(000) 000-0000.
How do I tell the mail merge to skip blank cells in Excel so I see
nothing? I prefer to not change my data source (say to text to fix
this) in Excel as it is a "live" spreadsheet that users are
continously adding data to to fill in the missing records for phone
number and DOB.

My guess is the problem is the same in either case and that is due to the
spaces in your fieldnames. I would also suggest fieldnames of ten characters
or less. In the short term, putting straight quotes around the fieldnames
should work for the switch option -
{IF {MERGEFIELD "PHONE H PRE TEXT"} "(000) 000-0000" "{MERGEFIELD "PHONE
H PRE TEXT"}" }

--

Graham Mayor - Word MVP

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





Jim wrote:
Graham,

Thanks for the suggestions. I understand how each method appraches
the problem, but I have a few issues.

Going the DDE route, everything seems to work fine until trying to
view the merged document, Word crashes. My versions of Word and
Excel are both 2002. My data set in Excel is 58,000 records. Is one
of these the reason?

Using the conditional method, I must not be entering it correctly
because I get the result of PHONE when viewing the merged data in
Word instead of a phone number or nil.

I am entering the merge field (called PHONE H PRE TEXT), highlighting
it, right clicking, choosing Edit Field under Choose A Field and
picking IF, when the Advanced Field Properties text box comes up
begining with IF I then enter the expression as you intruscted for my
data: {IF {MERGEFIELD PHONE H PRE TEXT} "(000) 000-0000"
"{MERGEFIELD PHONE H PRE TEXT}" }

What am I doing wrong?
Do you have step by step instructions for using conditionals?

Thank you,
Jim

"Graham Mayor" wrote:

You could use conditionals field eg
{IF {Mergefield Phone} "(000) 000-0000" "{Mergefield Phone}"}
and
{IF {Mergefield DOB} "12:00:00 AM" "{Mergefield DOB}"}

or probably simpler, see the Excel data section of
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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





Jim wrote:
My original data source is Excel
I have two fields causing me problems - Phone & Date of Birth
Each field is numeric and formatted as Special Phone Number and Date
I only have about 1 out of 10 records with data
I am using mail merge to complete application forms using the data
in Excel I used switches on the phone field to make the data display
properly
My problems a
When I view the records in mail merge if there was no date for a
record in Excel, I see a time displayed 12:00:00 AM. When I view
the record where there is no phone number, I see my formatting
instead (000) 000-0000.
How do I tell the mail merge to skip blank cells in Excel so I see
nothing? I prefer to not change my data source (say to text to fix
this) in Excel as it is a "live" spreadsheet that users are
continously adding data to to fill in the missing records for phone
number and DOB.