Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
I have built an IF Then Else statement that successfully compares three
fields of date, each field with two options (6 statements) to see if a letter should be merged to a record. I want to skip the record if all six comparisons fail and a letter is not triggered. When I do the merge, I get a blank page every non-qualifying record. Can you help? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
There is a SKIPIF field that should cancel processing of the current record,
e.g. { SKIPIF { MERGEFIELD counter } 10 } Word Help says you shouldn't use this field type for new merges, and suggests that you filter records using Mail Merge Recipients. However, using a filter is not always an option, not least because Word does not always get it right if the filter conditions are date comparisons. So I think your only options are probably a. try the filter anyway - if it works, it works b. if possible, apply the filter in the data source, e.g. if it's an Access table and you can write a Query to do the correct filtering, you may be able to use that as the data source c. depending on the data source, you may be able to issue the SQL query you need to filter the data in a Word VBA OpenDataSource call d. use the { SKIPIF } approach anyway, but bear in mind that it may not work in future. Peter Jamieson "Nick" wrote in message ups.com... I have built an IF Then Else statement that successfully compares three fields of date, each field with two options (6 statements) to see if a letter should be merged to a record. I want to skip the record if all six comparisons fail and a letter is not triggered. When I do the merge, I get a blank page every non-qualifying record. Can you help? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Thank you for the reply. Need a Word Mail Merge (MM) "Skip Record IF"
solution. Data compare perception ratings between three products. The target product could be 1st, 2nd or 3rd choice, or not mentioned. If the product is rated either better or worse than the products in the other two positions a paragraph or letter is generated. The MM works fine for that purpose. However the merge is creating a blank page for the non-qualifying records. Need a way to Skip the record if: 1. product is not mentioned in any position or 2. product is mentioned but is not rated better or worse than the target product |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Mergefield Counter - Can you tell me how this works? Does it count the
number of IF statements and then go to the next record? This looks hopeful if I can figure out how to turn it on. I placed it at the head of my mail merge and tried 27 (the number of IF statements), 3 (the number of times MERGEFIELD appears in the mail merge statement, and even 1, but still am getting the unwanted blank merged pages when I run the merge. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Sorry, not sure what you are talking about here - what are you actually
inserting at the head of the mail merge? Peter Jamieson "Nick" wrote in message oups.com... Mergefield Counter - Can you tell me how this works? Does it count the number of IF statements and then go to the next record? This looks hopeful if I can figure out how to turn it on. I placed it at the head of my mail merge and tried 27 (the number of IF statements), 3 (the number of times MERGEFIELD appears in the mail merge statement, and even 1, but still am getting the unwanted blank merged pages when I run the merge. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Not sure what columns you have in your data source and your specification
seems ambiguous to me, but assuming you have target,choice_1,choice_2,choice_3,rating_1,rating_ 2,rating_3 then I think you need something like { SKIPIF { = { IF { MERGEFIELD target } = { MERGEFIELD choice_1 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_2 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_3 } "1" "0" } *{ IF { MERGEFIELD rating_1 } = { MERGEFIELD rating_2 } "{ IF { MERGEFIELD rating_2 } = { MERGEFIELD rating_3 } "0" "1" }" "1" } } = 0 } Peter Jamieson "Nick" wrote in message oups.com... Thank you for the reply. Need a Word Mail Merge (MM) "Skip Record IF" solution. Data compare perception ratings between three products. The target product could be 1st, 2nd or 3rd choice, or not mentioned. If the product is rated either better or worse than the products in the other two positions a paragraph or letter is generated. The MM works fine for that purpose. However the merge is creating a blank page for the non-qualifying records. Need a way to Skip the record if: 1. product is not mentioned in any position or 2. product is mentioned but is not rated better or worse than the target product |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Sorry to be ambiguous. I have been trying to figure out how I can copy
the mail merge instructions so you can see them, but can't figure it out, so, I will bite the bullet and type them in. Data fields include: ClientPosition (whether our client is the "1","2" or "3" choice or "FALSE" if not selected) Issue D rating for 1st choice client (1-5 reported where "1" is most favorable - up to "4" which is least favorable and "5" is no opinion) Issue D rating for 2nd choice client Issue D rating for 3rds choice client Mail merge does the following: 1. check to see if client position=1 2. checks to see that rating for 1st choice is not null 3. checks to see that rating for 2nd choice is not null 4. checks to see that rating for 1st choice is less than 5 5. subtracts value of 1st choice rating from value of second choice rating to see if it is less than zero 6. If it is the text is activated. If it isn't it 7. checks to see if client position=2, etc. {IF {MERGEFIELD ClientPosition } = 1 "{IF {Q2_C2D } "" {IF {Q2_C1D } "" { IF {Q2_C1D } 5 { IF { = { Q2_C2D } - { Q2_C1D } } 0 "Text here for client in first position rated lower than competitor in second position " "{ IF {Q2_3D } "" { IF { Q2_C1D} "" { IF { Q2_C1D } 5 { IF { = { Q2_C3D } - {Q2_C1D } } 0 "Text here for client in first position rated lower than competitor in third position " "" } } } } " } } } } }{IF {MERGEFIELD ClientPosition } = 2 "{IF {Q2_C2D } "" {IF {Q2_C1D } "" { IF {Q2_C2D } 5 { IF { = { Q2_C1D } - { Q2_C1D } } 0 "Text here for client in second position rated lower than competitor in first position " "{ IF {Q2_2D } "" { IF { Q2_C3D} "" { IF { Q2_C2D } 5 { IF { = { Q2_C3D } - {Q2_C2D } } 0 "Text here for client in second position rated lower than competitor in third position " "" } } } } " } } } } }, etc. I'm looking for a way to skip the record if none of the statements trigger the associated text. Thanks for looking at this for me. Nick King |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
To keep things simple, one way you /might/ be able to do it is as follows:
If your complete set of IF fields and text inserts no text at all (not even any spaces or paragraph marks) in the situation where you want to skip the letter, then copy the entire body of your mailmerge main document (probably except the last paragraph mark) and paste it into a SKIPIF field, i.e. as follows: { SKIPIF "all your fields and text" = "" }all your fields and text (i.e. if it works, it's simpler than trying to work out a second set of IF fields and so on). Peter Jamieson "Nick" wrote in message oups.com... Sorry to be ambiguous. I have been trying to figure out how I can copy the mail merge instructions so you can see them, but can't figure it out, so, I will bite the bullet and type them in. Data fields include: ClientPosition (whether our client is the "1","2" or "3" choice or "FALSE" if not selected) Issue D rating for 1st choice client (1-5 reported where "1" is most favorable - up to "4" which is least favorable and "5" is no opinion) Issue D rating for 2nd choice client Issue D rating for 3rds choice client Mail merge does the following: 1. check to see if client position=1 2. checks to see that rating for 1st choice is not null 3. checks to see that rating for 2nd choice is not null 4. checks to see that rating for 1st choice is less than 5 5. subtracts value of 1st choice rating from value of second choice rating to see if it is less than zero 6. If it is the text is activated. If it isn't it 7. checks to see if client position=2, etc. {IF {MERGEFIELD ClientPosition } = 1 "{IF {Q2_C2D } "" {IF {Q2_C1D } "" { IF {Q2_C1D } 5 { IF { = { Q2_C2D } - { Q2_C1D } } 0 "Text here for client in first position rated lower than competitor in second position " "{ IF {Q2_3D } "" { IF { Q2_C1D} "" { IF { Q2_C1D } 5 { IF { = { Q2_C3D } - {Q2_C1D } } 0 "Text here for client in first position rated lower than competitor in third position " "" } } } } " } } } } }{IF {MERGEFIELD ClientPosition } = 2 "{IF {Q2_C2D } "" {IF {Q2_C1D } "" { IF {Q2_C2D } 5 { IF { = { Q2_C1D } - { Q2_C1D } } 0 "Text here for client in second position rated lower than competitor in first position " "{ IF {Q2_2D } "" { IF { Q2_C3D} "" { IF { Q2_C2D } 5 { IF { = { Q2_C3D } - {Q2_C2D } } 0 "Text here for client in second position rated lower than competitor in third position " "" } } } } " } } } } }, etc. I'm looking for a way to skip the record if none of the statements trigger the associated text. Thanks for looking at this for me. Nick King |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
Peter -
In the solution below: { SKIPIF { = { IF { MERGEFIELD target } = { MERGEFIELD choice_1 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_2 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_3 } "1" "0" } *{ IF { MERGEFIELD rating_1 } = { MERGEFIELD rating_2 } "{ IF { MERGEFIELD rating_2 } = { MERGEFIELD rating_3 } "0" "1" }" "1" } there are some operators (+ and *). Can you tell me about other available operators? Is there an "or" operator? Nick King |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Skip Record if Comparison of Several Fields Fails
You can look up the available functions and operators in the Word help for
the = field. However, that can be hard to find as the field isn't listed in the help index - you can search for "formula field" in the "Search for" box in Word Help. + is just the usual addition operator and * is the usual multiplication operator. it is sometimes easier to use these rather than the or and and functions which are also available because or and and only take two parameters, i.e. you can have { =or(param1,param2) } but for three parameters I think you need { =or(param1,or(param1,param2)) } The thing to bear in mind is that within the formula field "True" is represented as "1" and "False" as "0", so in the first example above, if param1 or param2 is true or 1, then the result is "1". However, the fields as suggested here obviously won't work because your structures are actually slightly different. Peter Jamieson "Nick" wrote in message oups.com... Peter - In the solution below: { SKIPIF { = { IF { MERGEFIELD target } = { MERGEFIELD choice_1 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_2 } "1" "0" } +{ IF { MERGEFIELD target } = { MERGEFIELD choice_3 } "1" "0" } *{ IF { MERGEFIELD rating_1 } = { MERGEFIELD rating_2 } "{ IF { MERGEFIELD rating_2 } = { MERGEFIELD rating_3 } "0" "1" }" "1" } there are some operators (+ and *). Can you tell me about other available operators? Is there an "or" operator? Nick King |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I skip blank fields in mail merge | Mailmerge | |||
"Next Record If", Mail Merge Issues | Mailmerge | |||
How to do merge with varying fields in each record? | Mailmerge | |||
Fields inside footer -- Last record showing field instead of data | Mailmerge | |||
Repost: Record 1 contained too few data fields | Mailmerge |