Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
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! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
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] ------------------------- "Peter Jamieson" wrote in message ... 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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
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 "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
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] ------------------------- "Peter Jamieson" wrote in message ... 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 "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
Yes:-)
Peter Jamieson "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge - using multiple field switches
many thanks
nailed it - the letter's looking great! cheers Martin "Peter Jamieson" wrote in message ... Yes:-) Peter Jamieson "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "macropod" wrote in message ... 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] ------------------------- "Peter Jamieson" wrote in message ... 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 "Martin Jones" martinjones1260@gmailDOTcom wrote in message ... 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! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format switches-how do I use (attach to the merge field? | Mailmerge | |||
mail merge: printing multiple pages, or field calculation error | Microsoft Word Help | |||
mail merge field for telephone numbers; what switches? | Mailmerge | |||
Mail Merge Switches | Microsoft Word Help | |||
Mail Merge Switches | Microsoft Word Help |