Thanks again Peter - once again a perfect explanation.
Your explanation works perfectly with dates, however the same syntax doesn't
seem to work with time:
{IF "{MERGEFIELD Time}"="00:00" "N/A" "{MERGEFIELD Time\@HH:mm"}"}
We log a time if applicable and insert N/A if its not. It looks like we get
00:00 displayed in the merge field when the data source reads N/A
The above code simply leaves 00:00 as the time when it should say N/A unless
a legitimate time is in the data source
--
Regards
Andy
Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
What do you see when the value is ASAP and you use the mergefield with no
format definition, i.e.
{ MERGEFIELD "Date Required" }
?
If you are using Word 2002 or later and you used the default connection
method (OLE DB - if you don't know what connection method you used then
it's almost certainly OLE DB) then you will probably see a time such as
"12:00:00 AM"
In that case you can try
{ IF "{ MERGEFIELD "Date Required" }" = "12:00:00 AM" "ASAP" "{ MERGEFIELD
"Date Required" \@"DD/MM/YY" }" }
Each pair of {} needs to be the "special field braces" that you can insert
using ctrl-F9.
If you happen to have connected using ODBC (unlikely these days) then you
will probably see a blank result, and as long as you do not need to
distinguish between ASAP and a blank cell, you can use
{ IF "{ MERGEFIELD "Date Required" }" = "" "ASAP" "{ MERGEFIELD "Date
Required" \@"DD/MM/YY" }" }
If you happen to have connected using DDE (the default in Word 2000 and
earlier), then you should see "ASAP". In that case,
{ MERGEFIELD "Date Required" \@"DD/MM/YY" }
should display the correct result, but if not, you can try
{ IF "{ MERGEFIELD "Date Required" }" = "ASAP" "ASAP" "{ MERGEFIELD "Date
Required" \@"DD/MM/YY" }" }
Although you may be able to fix this by changing your connection method to
DDE, I find it difficult to recommend that these days.
See http://tips.pjmsn.meuk/t0003.htm for further info.
Peter Jamieson
http://tips.pjmsn.me.uk
On 25/03/2010 07:53, Andy Roberts wrote:
I have a spreadsheet which lists jobs and one of the columns is a
"required
date" value which sometimes is unknown so instead of a date we input
"ASAP" - this works fine in the spreadsheet.
I then have a mail merge document which brings in the "required date"
field
and is formatted as follow:-
{MERGEFIELD Date Required\@"DD/MM/YY"}
This works ok when it is merging a date but doesn't work when it finds
"ASAP" instead of a date. I understand that it is formatted to a date
format which is the problem but if I remove this formatting I get a US
date
format. I dont want to leave the excel cell blank as this looks as though
the inputter has missed something.
Is there anyway around this?