[Andy Roberts]

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?
--
Regards

Andy

Andy Roberts
Win XP, Office 2007

[Peter Jamieson]

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?

[Andy Roberts]

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?

[Andy Roberts]

Peter

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" 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?

[Peter Jamieson]

{{IF "{MERGEFIELD Type}"="E" "email"} {IF "{MERGEFIELD Type}"="L"
"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

http://tips.pjmsn.me.uk

On 25/03/2010 13:08, Andy Roberts wrote:
Peter

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!)

[Peter Jamieson]

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