Showing today’s date when the Excel value is zero

[David Hall]

Hi, this problem is really frustrating me so if you can help I would really
appreciate it.

I am using Word 2003 and Excel 2003 to do a mailmerge and I am having a
problem with today’s date being inserted in the Word document when the Excel
value is zero. I would either like to show the date that is entered in the
Excel sheet or nothing if the Excel value is zero.

My entry in the Word document is:
{IF{MERGEFIELD E_Start} = 0 "" {MERGEFIELD E_Start \@ "d MMMM yyyy}

Where am I going wrong?

Many thanks
David Hall

 

[Graham Mayor]

If today's date is being inserted into Word when there is a 0 in the data
file, then the merge is never seeing 0 as the result of the merge. You have
to check for what is *actually* being merged and not what you expect to be
merged.

What *exactly* does {Mergefield E_Start} produce?

You can then test for this instead of 0. If indeed it is today's date
(provided today's date is never E_start) then

{IF{MERGEFIELD E_Start} <> {Date}"{MERGEFIELD E_Start \@ "d MMMM yyyy"}"}

might be nearer the mark. You will probably have to add switches to both the
first E_Start and Date.

You may find it simpler to use an alternative means of connecting your merge
to the Excel data - see the Excel data section of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm

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


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

 

[David Hall]

Graham thank you for your comments.
Whenever a date is present in E_Start the correct date is inserted in the
Word Doc, unfortunately I was unable to achieve a solution with your
suggestion.

The mailmerge is executed from a sheet that contains one row of data. Which
information is in this row is determined by a selection process elsewhere in
the workbook. Therefore if the main data has a blank cell the corresponding
cell in the row for the merge data shows a zero.

I tried with a test Excel Doc and test Word Doc and got the same ‘error’.
I find it really weird that a zero in the Excel cell is ‘forcing’ today’s
date to be entered in the Word Doc.

Re-writing the Excel entry so that if the main data cell is equal to zero
(ie blank) the corresponding merge cell is to equal blank ( Ҡ) does solve
the problem. So perhaps this is the best solution.

Obviously if another solution is possible I would be very interested to
receiving further postings.

David Hall

 

[Graham Mayor]

I appreciate that when the field has content it produces the date, but it is
what the field produces when the data is 0 that matters. Word interprets the
raw data and not necessarily what you see displayed in Excel, which is based
on the formatting of the cell.
So what *exactly* does the field (without a switch) produce when the cell
content is 0? Today's date can be expressed in a variety of ways - how does
the field express it?
I have to say that correcting the Excel data is the better plan if that is
available to you. Not everyone has that option

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


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

 

[David Hall]

Graham, thanks again for persevering with this.

Removing the switch, saving the document and reopening (to make sure all
changes are applied) produces a field entry in Word of:
12:00:00 AM
When the Excel value is 0.

When I remove the date formatting from the Excel cell then the Word field
shows zero if the Excel value is zero.
However, the result when the Excel cell contains a date the Word field
contains the numerical value of the date, ie 36008 for 1st August 1998. This
result is unaffected by inserting the switch! (Again I saved and reopened the
documents)

I hope this helps you diagnose what I have got wrong.

David Hall

 

[Doug Robbins - Word MVP]

Use an If...Then...Else field that checks for "12:00:00 AM" being returned

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Graham Mayor]

What you need is:
{IF{MERGEFIELD E_Start} <> "12:00:00 AM" {MERGEFIELD E_Start \@ "d MMMM
yyyy"}"}


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


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

 

[David Hall]

Graham
Fantastic, that works.

Thank you
David Hal