Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / March 2007

Tip: Looking for answers? Try searching our database.

Mail merge - using multiple field switches

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Martin Jones - 11 Mar 2007 19:33 GMT
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 - 11 Mar 2007 22:39 GMT
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
>
[quoted text clipped - 24 lines]
>
> PS: Thanks to Peter Jamieson for his previous help!
macropod - 12 Mar 2007 08:41 GMT
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

Signature

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.
[quoted text clipped - 48 lines]
>>
>> PS: Thanks to Peter Jamieson for his previous help!
Peter Jamieson - 12 Mar 2007 09:53 GMT
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,
>
[quoted text clipped - 77 lines]
>>>
>>> PS: Thanks to Peter Jamieson for his previous help!
macropod - 12 Mar 2007 10:53 GMT
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

Signature

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

> Hi Macropod,
>
[quoted text clipped - 70 lines]
>>>>
>>>> PS: Thanks to Peter Jamieson for his previous help!
Peter Jamieson - 12 Mar 2007 16:58 GMT
Yes:-)

Peter Jamieson

> Hi Peter,
>
[quoted text clipped - 103 lines]
>>>>>
>>>>> PS: Thanks to Peter Jamieson for his previous help!
Martin Jones - 12 Mar 2007 22:12 GMT
many thanks

nailed it - the letter's looking great!

cheers
Martin

> Yes:-)
>
[quoted text clipped - 107 lines]
>>>>>>
>>>>>> PS: Thanks to Peter Jamieson for his previous help!

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.