The equivalent of the "date column" problem would actually be
{ IF "{ MERGFIELD Time }" = "12:00:00 AM" "N/A" "{ MERGEFIELD Time
\@HH:mm"}"}
If you have a column that the OLE DB provider decides is a date/time
column, then
a. a cell that contains blank (i.e. the content has been cleared)
should appear as blank in Word
b. a cell containing text should appear as 12:00:00 AM in Word (and
that's regardless of your regional settings AFAIK).
c. a cell that contains the time 00:00 will also show in Word as
12:00:00 AM.
So there's no way to distinguish between a text and a 00:00 time.
Incidentally, if you are really using the spreadsheet function =NA() to
record a non-existent time, not the text "N/A", things are slightly
nastier because how /that/ displays depends on whether or not the
workbook is already open in Excel when Word connects to it:
d. If the workbook is already open, at least some error values such as
#N/A and #DIV/0! will probably display as a blank in Word (i.e. same as
if you have a blank cell in Excel, not the same as if you have a the
text "N/A")
e. If the workbook is closed, at least some error values such as #N/A
and #DIV/0! will probably display as "12:00:00 AM" in Word (i.e. same as
if you have a piece of text in the cell).
I really ought to cover that on my web page.
Peter Jamieson
http://tips.pjmsn.me.uk
On 25/03/2010 12:48, Andy Roberts wrote:
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