Mail merge - using multiple field switches

[Martin Jones]

Hi

I have successfully created a mail merge letter that includes the following
field:

{ MERGEFIELD "Bonus" \b "in recognition of your peformance in 2006, you will
receive a bonus payment of £" \f". " } where "Bonus" is the datafield in
my Excel database.

Can anybody tell me how to also incorporate a switch to format the bonus
value that is picked up from the Excel database that feeds the merge? At
present, amounts like £37.50 are merging as "£37.5", without the zero in the
50. I could use a Word Find/Replace to change ".5." to ".50", but I would
like to automate it during the merge itself if at all possible

I think I know which switch to include - had planned on using this one: \#
##,###.## - but I don't know where I should include it in the above and
whether it should be immediately adjacent to the other characters or
separated by a space! If that switch/type of switch is wrong, please let me
know! None of the webpages I have looked at indicate how to combine multiple
field switches.

many thanks in advance

kind regards
Martin


PS: Thanks to Peter Jamieson for his previous help!

 

[Peter Jamieson]

The problem with combining \b, \f and \# is that \b (at least) is applied
first, then \# is applied to the result. Typically, most of the text in your
\b switch will be discarded and...well, it just won't be what you want.

To go back to my post of 02 Nov 2006 on this general subject, what to do
depends on exactly what can be in
"Bonus" and how you want it displayed. In this case I think it's fair to
assume that "Bonus" could
have the following types of content:
1. numeric, non-zero, positive, e.g. 1.234
2. numeric, zero, e.g. 0
3. blank (could come from a numeric column where Word or the data source has
not substitued a "0"), i.e. ""
Using \b and \# suggests you want the following:

1. in recognition of your peformance in 2006, you will receive a bonus
payment of £1.23.
2. and 3. <blank>
(If you want to deal with a negative value maybe it shouldn't be named
"Bonus" )
If so, I suggest you try the following fields:
( SET mybonus { MERGEFIELD Bonus \#"£,0.00;;" }
}{ IF "{ REF mybonus }" = ""
""
"in recognition of your peformance in 2006, you will receive a bonus payment
of { REF mybonus }." }

(all the {} need to be the special "field code braces" you can insert using
ctrl-F9).

Ideally you would be able to do this much more simply using

{ MERGEFIELD Bonus \#"'in recognition of your peformance in 2006, you will
receive a bonus payment of '£,0.00;;" }

or by applying the \b and \f switches to the { REF bonus } field, but those
switches are only available in the MERGEFIELD field.

Peter Jamieson

 

[macropod]

Hi Peter,

In my experience, you can only have 64 characters between the double quotes in a numeric picture switch.

To overcome that, I'd suggest coding the field as:
{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you will receive a bonus payment of { MERGEFIELD Bonus}." ""}

Cheers

 

[Peter Jamieson]

Hi Macropod,

Yes, much neater. But then it needs to be more like

{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you
will receive a bonus payment of { MERGEFIELD Bonus \#"£,0.00;;" }." ""}

I think

Peter Jamieson

Hi Peter,

In my experience, you can only have 64 characters between the double
quotes in a numeric picture switch.

To overcome that, I'd suggest coding the field as:
{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you
will receive a bonus payment of { MERGEFIELD Bonus}." ""}

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

The problem with combining \b, \f and \# is that \b (at least) is applied
first, then \# is applied to the result. Typically, most of the text in
your \b switch will be discarded and...well, it just won't be what you
want.

To go back to my post of 02 Nov 2006 on this general subject, what to do
depends on exactly what can be in
"Bonus" and how you want it displayed. In this case I think it's fair to
assume that "Bonus" could
have the following types of content:
1. numeric, non-zero, positive, e.g. 1.234
2. numeric, zero, e.g. 0
3. blank (could come from a numeric column where Word or the data source
has
not substitued a "0"), i.e. ""
Using \b and \# suggests you want the following:

1. in recognition of your peformance in 2006, you will receive a bonus
payment of £1.23.
2. and 3. <blank>
(If you want to deal with a negative value maybe it shouldn't be named
"Bonus" )
If so, I suggest you try the following fields:
( SET mybonus { MERGEFIELD Bonus \#"£,0.00;;" }
}{ IF "{ REF mybonus }" = ""
""
"in recognition of your peformance in 2006, you will receive a bonus
payment of { REF mybonus }." }

(all the {} need to be the special "field code braces" you can insert
using ctrl-F9).

Ideally you would be able to do this much more simply using

{ MERGEFIELD Bonus \#"'in recognition of your peformance in 2006, you
will receive a bonus payment of '£,0.00;;" }

or by applying the \b and \f switches to the { REF bonus } field, but
those switches are only available in the MERGEFIELD field.

Peter Jamieson

 

[macropod]

Hi Peter,

Since we're already using an IF test to make sure the bonus is greater than 0, there's no need for the negative an 0 arguments in
the picture switch. So:

{IF{MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you will receive a bonus payment of {MERGEFIELD Bonus \#
£,0.00}." ""}

;-)

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

Hi Macropod,

Yes, much neater. But then it needs to be more like

{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you will receive a bonus payment of { MERGEFIELD Bonus
\#"£,0.00;;" }." ""}

I think

Peter Jamieson

Hi Peter,

In my experience, you can only have 64 characters between the double quotes in a numeric picture switch.

To overcome that, I'd suggest coding the field as:
{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you will receive a bonus payment of { MERGEFIELD Bonus}."
""}

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

The problem with combining \b, \f and \# is that \b (at least) is applied first, then \# is applied to the result. Typically,
most of the text in your \b switch will be discarded and...well, it just won't be what you want.

To go back to my post of 02 Nov 2006 on this general subject, what to do depends on exactly what can be in
"Bonus" and how you want it displayed. In this case I think it's fair to assume that "Bonus" could
have the following types of content:
1. numeric, non-zero, positive, e.g. 1.234
2. numeric, zero, e.g. 0
3. blank (could come from a numeric column where Word or the data source has
not substitued a "0"), i.e. ""
Using \b and \# suggests you want the following:

1. in recognition of your peformance in 2006, you will receive a bonus payment of £1.23.
2. and 3. <blank>
(If you want to deal with a negative value maybe it shouldn't be named "Bonus" )
If so, I suggest you try the following fields:
( SET mybonus { MERGEFIELD Bonus \#"£,0.00;;" }
}{ IF "{ REF mybonus }" = ""
""
"in recognition of your peformance in 2006, you will receive a bonus payment of { REF mybonus }." }

(all the {} need to be the special "field code braces" you can insert using ctrl-F9).

Ideally you would be able to do this much more simply using

{ MERGEFIELD Bonus \#"'in recognition of your peformance in 2006, you will receive a bonus payment of '£,0.00;;" }

or by applying the \b and \f switches to the { REF bonus } field, but those switches are only available in the MERGEFIELD field.

Peter Jamieson

Hi

I have successfully created a mail merge letter that includes the following field:

{ MERGEFIELD "Bonus" \b "in recognition of your peformance in 2006, you will receive a bonus payment of £" \f". " } where
"Bonus" is the datafield in my Excel database.

Can anybody tell me how to also incorporate a switch to format the bonus value that is picked up from the Excel database that
feeds the merge? At present, amounts like £37.50 are merging as "£37.5", without the zero in the 50. I could use a Word
Find/Replace to change ".5." to ".50", but I would like to automate it during the merge itself if at all possible

I think I know which switch to include - had planned on using this one: \# ##,###.## - but I don't know where I should include
it in the above and whether it should be immediately adjacent to the other characters or separated by a space! If that
switch/type of switch is wrong, please let me know! None of the webpages I have looked at indicate how to combine multiple
field switches.

many thanks in advance

kind regards
Martin


PS: Thanks to Peter Jamieson for his previous help!

 

[Peter Jamieson]

Yes

Peter Jamieson

Hi Peter,

Since we're already using an IF test to make sure the bonus is greater
than 0, there's no need for the negative an 0 arguments in the picture
switch. So:

{IF{MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you
will receive a bonus payment of {MERGEFIELD Bonus \# £,0.00}." ""}

;-)

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

Hi Macropod,

Yes, much neater. But then it needs to be more like

{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006,
you will receive a bonus payment of { MERGEFIELD Bonus \#"£,0.00;;" }."
""}

I think

Peter Jamieson

Hi Peter,

In my experience, you can only have 64 characters between the double
quotes in a numeric picture switch.

To overcome that, I'd suggest coding the field as:
{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006,
you will receive a bonus payment of { MERGEFIELD Bonus}." ""}

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

The problem with combining \b, \f and \# is that \b (at least) is
applied first, then \# is applied to the result. Typically, most of the
text in your \b switch will be discarded and...well, it just won't be
what you want.

To go back to my post of 02 Nov 2006 on this general subject, what to
do depends on exactly what can be in
"Bonus" and how you want it displayed. In this case I think it's fair
to assume that "Bonus" could
have the following types of content:
1. numeric, non-zero, positive, e.g. 1.234
2. numeric, zero, e.g. 0
3. blank (could come from a numeric column where Word or the data
source has
not substitued a "0"), i.e. ""
Using \b and \# suggests you want the following:

1. in recognition of your peformance in 2006, you will receive a bonus
payment of £1.23.
2. and 3. <blank>
(If you want to deal with a negative value maybe it shouldn't be named
"Bonus" )
If so, I suggest you try the following fields:
( SET mybonus { MERGEFIELD Bonus \#"£,0.00;;" }
}{ IF "{ REF mybonus }" = ""
""
"in recognition of your peformance in 2006, you will receive a bonus
payment of { REF mybonus }." }

(all the {} need to be the special "field code braces" you can insert
using ctrl-F9).

Ideally you would be able to do this much more simply using

{ MERGEFIELD Bonus \#"'in recognition of your peformance in 2006, you
will receive a bonus payment of '£,0.00;;" }

or by applying the \b and \f switches to the { REF bonus } field, but
those switches are only available in the MERGEFIELD field.

Peter Jamieson

Hi

I have successfully created a mail merge letter that includes the
following field:

{ MERGEFIELD "Bonus" \b "in recognition of your peformance in 2006,
you will receive a bonus payment of £" \f". " } where "Bonus" is the
datafield in my Excel database.

Can anybody tell me how to also incorporate a switch to format the
bonus value that is picked up from the Excel database that feeds the
merge? At present, amounts like £37.50 are merging as "£37.5", without
the zero in the 50. I could use a Word Find/Replace to change ".5." to
".50", but I would like to automate it during the merge itself if at
all possible

I think I know which switch to include - had planned on using this
one: \# ##,###.## - but I don't know where I should include it in the
above and whether it should be immediately adjacent to the other
characters or separated by a space! If that switch/type of switch is
wrong, please let me know! None of the webpages I have looked at
indicate how to combine multiple field switches.

many thanks in advance

kind regards
Martin


PS: Thanks to Peter Jamieson for his previous help!

 

[Martin Jones]

many thanks

nailed it - the letter's looking great!

cheers
Martin

Yes

Peter Jamieson

Hi Peter,

Since we're already using an IF test to make sure the bonus is greater
than 0, there's no need for the negative an 0 arguments in the picture
switch. So:

{IF{MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006, you
will receive a bonus payment of {MERGEFIELD Bonus \# £,0.00}." ""}

;-)

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

Hi Macropod,

Yes, much neater. But then it needs to be more like

{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006,
you will receive a bonus payment of { MERGEFIELD Bonus \#"£,0.00;;" }."
""}

I think

Peter Jamieson

Hi Peter,

In my experience, you can only have 64 characters between the double
quotes in a numeric picture switch.

To overcome that, I'd suggest coding the field as:
{IF{ MERGEFIELD Bonus}> 0 "In recognition of your performance in 2006,
you will receive a bonus payment of { MERGEFIELD Bonus}." ""}

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

The problem with combining \b, \f and \# is that \b (at least) is
applied first, then \# is applied to the result. Typically, most of
the text in your \b switch will be discarded and...well, it just won't
be what you want.

To go back to my post of 02 Nov 2006 on this general subject, what to
do depends on exactly what can be in
"Bonus" and how you want it displayed. In this case I think it's fair
to assume that "Bonus" could
have the following types of content:
1. numeric, non-zero, positive, e.g. 1.234
2. numeric, zero, e.g. 0
3. blank (could come from a numeric column where Word or the data
source has
not substitued a "0"), i.e. ""
Using \b and \# suggests you want the following:

1. in recognition of your peformance in 2006, you will receive a bonus
payment of £1.23.
2. and 3. <blank>
(If you want to deal with a negative value maybe it shouldn't be named
"Bonus" )
If so, I suggest you try the following fields:
( SET mybonus { MERGEFIELD Bonus \#"£,0.00;;" }
}{ IF "{ REF mybonus }" = ""
""
"in recognition of your peformance in 2006, you will receive a bonus
payment of { REF mybonus }." }

(all the {} need to be the special "field code braces" you can insert
using ctrl-F9).

Ideally you would be able to do this much more simply using

{ MERGEFIELD Bonus \#"'in recognition of your peformance in 2006, you
will receive a bonus payment of '£,0.00;;" }

or by applying the \b and \f switches to the { REF bonus } field, but
those switches are only available in the MERGEFIELD field.

Peter Jamieson

Hi

I have successfully created a mail merge letter that includes the
following field:

{ MERGEFIELD "Bonus" \b "in recognition of your peformance in 2006,
you will receive a bonus payment of £" \f". " } where "Bonus" is
the datafield in my Excel database.

Can anybody tell me how to also incorporate a switch to format the
bonus value that is picked up from the Excel database that feeds the
merge? At present, amounts like £37.50 are merging as "£37.5",
without the zero in the 50. I could use a Word Find/Replace to change
".5." to ".50", but I would like to automate it during the merge
itself if at all possible

I think I know which switch to include - had planned on using this
one: \# ##,###.## - but I don't know where I should include it in the
above and whether it should be immediately adjacent to the other
characters or separated by a space! If that switch/type of switch is
wrong, please let me know! None of the webpages I have looked at
indicate how to combine multiple field switches.

many thanks in advance

kind regards
Martin


PS: Thanks to Peter Jamieson for his previous help!