Formatting Fields with MERGEFORMAT

Question

I am working on a mail merge document for a charitable donation receipt. In the table of the document, if "goods and services" were exchanged, I would like the value or costs of the item to be displayed from the field on an Excel spreadsheet. If there is a value, in one column it would trigger a text that reads "Less goods or services exchaged" and bring the value of the exchange in the field next to it. That works. In addition, I would like to use the following formula ({MERGEFIELD  Less_Goods_and_Services_Exchng 12.# "$0.00" \* MERGEFORMAT }) in a way that if there is no currency value entered in the goods or services field from the Excel spreadsheet, that the field would remain blank and not show the "$0.00 " value from the formatting. Can you please help?

Answer 1

1. It's a lot easier to see what's going on if you can press Alt-F9 while displaying your Mail Merge Main Document and see what the underlying field codes actually are. The view that shows the chevrons like this

«Less_Goods_and_Services_Exchng»

does not tell you anything about other aspects of the field code, e.g. it would appear the same whether the underlying field code was

{ MERGEFIELD Less_Goods_and_Services_Exchng }

or

{ MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00;;" }

2. Your original question was to do with ensuring that Less_Goods_and_Services_Exchng displayed as blank/null if it was zero, and the replies you received should have coped with that. But if you have

{ IF  { MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00;;" } = 0 "zero" "something" }

it won't work as you might hope because when

{ MERGEFIELD Less_Goods_and_Services_Exchng } is 0,

{ MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00;;" } will be "" and will not = 0

So you probably need something more like Jeeped's original suggestion, e.g.

{ IF { MERGEFIELD Less_Goods_and_Services_Exchng } = 0 "Thank you for your donation. No goods or services were exchanged for this contribution. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records."

"Thank you for your donation. Goods or services were exchanged for this contribution. We estimate that the fair market value of this exchange is { MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00" }, so your charitable contribution is { MERGEFIELD Total_Contribution # "$,0.00" }. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records." }

If you also have to deal with the case where { MERGEFIELD Less_Goods_and_Services_Exchng } is blank/null, and you want to deal with that case as if the value is 0, you need something more like

{ IF { ={ MERGEFIELD Less_Goods_and_Services_Exchng } - 0 } = 0 "Thank you for your donation. No goods or services were exchanged for this contribution. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records."

"Thank you for your donation. Goods or services were exchanged for this contribution. We estimate that the fair market value of this exchange is { MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00" }, so your charitable contribution is { MERGEFIELD Total_Contribution # "$,0.00" }. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records." }

(If you also need to deal with negative values of Less_Goods_and_Services_Exchng and negative or zero values of Total_Contribution, you shpuld be able to do that either using the other "branches" of the numeric field switch, or using additional tests. e.g. something roughly like

{ IF { ={ MERGEFIELD Less_Goods_and_Services_Exchng } - 0 } = 0 "Thank you for your donation. No goods or services were exchanged for this contribution. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records."

"Thank you for your donation. Goods or services were exchanged for this contribution. We estimate that the fair market value of this exchange is { MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00;-$,0.00" }, so your charitable contribution is { MERGEFIELD Total_Contribution # "$,0.00;-$,0.00;$0.00" }. This contribution is deductible to the fullest extent allowable under tax law. Please retain this letter for your records." }

)

Answer 2

Since the OP wants a blank if the amount ins 0, I'd go for:

{ MERGEFIELD Less_Goods_and_Services_Exchng # "$,0.00;;" }

Answer 3

... Now instead of showing "$0.00" it now it shows "MERGEFIELD" in both documents; the one with the field with the currency value and the document without the currency value ... 

When you manually modify a Word field, you need to tap Ctrl+F9 to insert a pair of field braces (e.g. { and }). You cannot simply type them in. If done correctly, bold field braces will be inserted and you can start typing within them. Try this sequence,

Ctrl+F9·I·F·<space>·Ctrl+F9·M·E·R·G·E·F·I·E·L·D·<space>·<type fieldname>·<End>·<space>·>·<space>·0·<space>·Ctrl+F9·M·E·R·G·E·F·I·E·L·D·<space>·<type fieldname and field format> ·<End> ·<space> ·<quote> ·<quote>·F9·<End>

You should end up with the following:

{ IF { MERGEFIELD Less_Goods_and_Services_Exchng } > 0 { MERGEFIELD  Less_Goods_and_Services_Exchng 12.# "$0.00" \* MERGEFORMAT } "" } 

 If necessary, tap Alt+F9 to display field results rather than field codes. Use Preview Results to cycle through the merge.

Answer 4

Yeah...I agree. That was weird. I am not sure what the 12. is for either. The previous MERGEFIELD value has an 11 in the formula. It showed up after I updated the field.

Anyways, I tried the formula that you suggested. Now instead of showing "$0.00" it now it shows "MERGEFIELD" in both documents; the one with the field with the currency value and the document without the currency value. Let me know if there is another way.

Thanks!

Answer 5

... that if there is no currency value entered in the goods or services field from the Excel spreadsheet, that the field would remain blank and not show the "$0.00 " value from the formatting ...

Use the { IF } expression field to avoid displaying the $0.00. Tap Alt+F9 and modify your MERGEFIELD ti something like,

{ IF { MERGEFIELD Less_Goods_and_Services_Exchng } > 0 { MERGEFIELD  Less_Goods_and_Services_Exchng 12.# "$0.00" \* MERGEFORMAT } "" } 

To be honest, I'm not entirely sure what that 12. in 12.# "$0.00" is supposed to do, but perhaps you can adapt the above to suit.

Field codes: MergeField field

Field codes: IF field

Examples of IF fields

Format field results

Format merged data