Date & Text format in Mail merge
Andy Roberts
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?
--
Regards
Andy
Andy Roberts
Win XP, Office 2007
Peter Jamieson
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
Andy Roberts
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" <p...@KillMAPSpjjnet.demon.co.uk> wrote in message
news:%23Yt6Lw$yKHA...@TK2MSFTNGP06.phx.gbl...
Andy Roberts
On a similar theme I presume I can use the IF command to check for different
values and replace accordingly. For exapmple one of my merge fields called
TYPE contains either E, L or F (email, letter, fax)
Thinking about it I presume I can insert the TYPE filed into a sentence
(e.g. "thank you for your "TYPE" ... but replace the relevant letter with a
piece of text (i.e. "thank you for your E" would become "thank you for your
email"...
The code im using is
{IF "{MERGEFIELD Type}"="E" "email" "="L" "letter"}
This only deals with the first replacement (i.e. the E)
I've also tried
{{IF "{MERGEFIELD Type}"="E" "email"} {IF "{MERGEFIELD Type}"="L" "letter"}
but I get nothing at all when I use this. I've learnt loads this morning
playing with this and I presume the problem is simply my syntax (and my lack
of knowledge!)
--
Regards
Andy
Andy Roberts
Win XP, Office 2007
"Peter Jamieson" <p...@KillMAPSpjjnet.demon.co.uk> wrote in message
news:%23Yt6Lw$yKHA...@TK2MSFTNGP06.phx.gbl...
Peter Jamieson
"letter"}
This looks as if it is wrapped in an extra pair of field braces (i.e. it
is probably actually something more like
{{IF "{MERGEFIELD Type}"="E" "email"} {IF "{MERGEFIELD Type}"="L" "letter"}}
which will display nothing.
However, you had the right idea as
{IF "{MERGEFIELD Type}" = "E" "email"} {IF "{MERGEFIELD Type}" = "L"
"letter"}
should do it.
> {IF "{MERGEFIELD Type}"="E" "email" "="L" "letter"}
The syntax is basically
{ IF operand1 operator operand2 true-result false-result }
or
{ IF operand1 operator operand2 true-result }
so the above would not work: you can nest, but typically you have to
spell things out, e.g.
{ IF "{ MERGEFIELD Type }" = "E" "email" "{ IF "{ MERGEFIELD Type }" =
"L" "letter" }" "something else" }
(It's advisable to put double-quotes around text operators but in many
situations they are not strictly essential)
See Graham Mayor's page at
http://www.gmayor.com/formatting_word_fields.htm
for other handy info. - I'd suggest you browse through
http://www.gmayor.com/Word_pages.htm
Peter Jamieson
Peter Jamieson
{ 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