Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Hall
 
Posts: n/a
Default Showing todays date when the Excel value is zero

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 todays 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


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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




  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Hall
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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 todays
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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





  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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

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


David Hall wrote:
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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



  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Hall
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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


"Graham Mayor" wrote:

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

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


David Hall wrote:
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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






  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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

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


"Graham Mayor" wrote:

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

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


David Hall wrote:
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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






  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default Showing today's date when the Excel value is zero

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


--

Graham Mayor - Word MVP

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



David Hall wrote:
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


"Graham Mayor" wrote:

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

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


David Hall wrote:
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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



  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Hall
 
Posts: n/a
Default Showing today's date when the Excel value is zero

Graham
Fantastic, that works.

Thank you
David Hal


"Graham Mayor" wrote:

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


--

Graham Mayor - Word MVP

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



David Hall wrote:
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


"Graham Mayor" wrote:

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

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


David Hall wrote:
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" wrote:

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_lab...th_word_xp.htm

--

Graham Mayor - Word MVP

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



David Hall wrote:
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




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
The Web Archive Converter puts todays date on MHT copies of old f zzz Microsoft Word Help 1 December 12th 05 01:56 PM
Mail Merge of a Date from Excel Data into Microsoft Word Keith A Microsoft Word Help 2 August 31st 05 05:01 AM
Excel date format merging to Word XP version Linda - Xtremly Peeved Mailmerge 11 April 12th 05 06:50 AM
How to prevent Merged Date Field showing time? jmasood Mailmerge 1 February 21st 05 12:59 PM
Date issue merging from Excel field in Date format to Word Barb Reinhardt Mailmerge 1 December 17th 04 08:34 AM


All times are GMT +1. The time now is 10:01 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"