How to remove unwanted formatting in mail merge for cells witn no

[Jim]

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 are:
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 Mayor]

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_labels_with_word_xp.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Jim]

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]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>