printing if then fields in a mail merge from acces (2000)

Topic

New Reply

Nearing Christmas again and trying to solve one more problem. Each child served has one letter merged about that child. For teens, people want to know if the child already has things like watch, tapeplayer or if they have pierced ears— these fields are not applicable to preschoolers so when I have used “if ..then ” to state “This child has(or does not have) a watch; I get odd reactions to letters about 2 yr old even though I have this in a box saying it relates to teens. I think I will change to a text field (in Access) and have “has” does not have” and Not applicable as the possibilities and simply have a straight merge. What I would prefer, would be to illiminate the information on letters for young kids. Any suggestions?

Reply | Quote

Replies

There are a couple of ways to approach this – one is to do separate letters for teens and younger children. The other is to use an IF … Then … Else … field in your word merge document, and put different text depending on whether they are a teen or preschooler. The second approach is a bit more complicated but lets you work with a single merge letter. I gues there is another way as well. You could construct the text of the Has/DoesNotHave section of your letter as a text string in a query and just merge that result to a single field in your merge document.

Reply | Quote

Dear Wendell– yes, I was using if then else– and I can do that–but as I mentioned–it still leaves me with childrens letters saying that they do not have a watch– would rather not have that on a 2 year old letter. Dividing teen and childrens letters would have some merit–but , I want the families printed at the same time so they can be kept together (it seems to work best when group sponsors have the whole family.) (I am sorry that I did not mention that in the original post) What I want, is to have those four answers not appear at all on the kids letters. I may just have to go with a three response answer and have one of the possibilities be Not Applicable. I will have to make this decision in the next two days as there are already many forms to enter. Thanks for your response.

Reply | Quote

Sorry – I should have been more specific. The trick is to embed the individual IF…THENs into another IF…THEN which uses a field code to display those fields if a teen is involved, and not display them otherwise. It’s a bit tricky to say, and you have to be sure all field codes are visible before you do it, but we’ve done very much the same thing where some people had to meet certain criteria and get one message, and another set get a different and more complex set based on their situation. Hope this make sense. If not, try attaching your Word merge document, and I’ll try to mock up what I have in mind.

Reply | Quote

that sounds like EXACTLY what I wanted– do not know how to embed one “if then else” into another–any help you can offer would be MUCH appreciated– right now I have a yes no field on each of the four questions– would like it to not print if the child was over a certain age (say 10) and print “this child Does(or this child does not) have a watch. I am off to teach a class and will return about 10 tonight (it is 5 something now) there is a DOB field to identify the ages.

Reply | Quote

I got sidetracked yesterday by other things, but this is a sample of what I meant by nested IF statements:
(When you view the attachment, you will need to highlight the “less”, right click and select “Toggle Field Codes”.)

Reply | Quote

could you make that example a bit more clear– I have NO idea what code you have written or where it is– I have used the built in features of word — to insert an if then else statement– I would REALLY like to use your idea, but dont know how to do it

Reply | Quote

maybe I explained poorly– in these particular 4 fields : watch—pierced ears—tape player– and CD player — the database allows yes or no (a check box) currently I have an if then else statement in the merge letter that says “This child does not have a watch” or “This child does own a watch” depending on the info in the database about that child. What I would like is for there to be NO information printed at all if the child was under ten– or it could say “this is a preteen; no information available ” or information not provided for younger children…. and I could accomplish the same by having Not Applicable as a text field along with Yes and NO for each question. Then the letter would not need an if then field– it could simply say Child has a watch (yes) or (no) or (Not Applicable) Was just hoping for the first option.

Reply | Quote

maybe I explained poorly– in these particular 4 fields : watch—pierced ears—tape player– and CD player — the database allows yes or no (a check box) currently I have an if then else statement in the merge letter that says “This child does not have a watch” or “This child does own a watch” depending on the info in the database about that child. What I would like is for there to be NO information printed at all if the child was under ten– or it could say “this is a preteen; no information available ” or information not provided for younger children…. and I could accomplish the same by having Not Applicable as a text field along with Yes and NO for each question. Then the letter would not need an if then field– it could simply say Child has a watch (yes) or (no) or (Not Applicable) Was just hoping for the first option.

Reply | Quote

Actually there isn’t any code (at least VBA) in what I did, and if you can’t get it to expand, then you can’t really see what’s going on. Let me try to give you a process to do it in a word document:

1. Open a blank Word document and insert an IF field in the document that looks something like:
   { IF DOB < 12/31/2000 "Text String If True" "Text String If False" }
2. Now turn the document into a Word Merge document so you can insert merge fields
3. Now replace the words Text String If True (but not the quotes) with the IF statements you have been using to display whether or not a given teen had a watch or not, a tape player or not, and so on
4. Finally replace the words Text String If False (again leave the quotes) with whatever you want to display for pre-teens.

The field codes I ended up with looked something like:
{ IF { MERGEFIELD DOB * MERGEFORMAT } < "12/31/2000" "{ IF {MERGEFIELD HASWATCH * MERGEFORMAT } "has watch" "does not have watch
" } { IF {MERGEFIELD HASTPLAYER * MERGEFORMAT } "has tape player" "does not have tape player"" "This child is a preteen" }
You could actually do a series of embedded IF statements for each article, but one of the tricky aspects is to not leave blank lines, and the approach I suggested can do it, though it gets pretty messy trying to make sure you put returns where you need them. Also note that you can’t simply paste in the statements from this post – Word needs to insert them as fields. Hope this makes it a little clearer, but on the other hand

Reply | Quote

Actually there isn’t any code (at least VBA) in what I did, and if you can’t get it to expand, then you can’t really see what’s going on. Let me try to give you a process to do it in a word document:

1. Open a blank Word document and insert an IF field in the document that looks something like:
   { IF DOB < 12/31/2000 "Text String If True" "Text String If False" }
2. Now turn the document into a Word Merge document so you can insert merge fields
3. Now replace the words Text String If True (but not the quotes) with the IF statements you have been using to display whether or not a given teen had a watch or not, a tape player or not, and so on
4. Finally replace the words Text String If False (again leave the quotes) with whatever you want to display for pre-teens.

The field codes I ended up with looked something like:
{ IF { MERGEFIELD DOB * MERGEFORMAT } < "12/31/2000" "{ IF {MERGEFIELD HASWATCH * MERGEFORMAT } "has watch" "does not have watch
" } { IF {MERGEFIELD HASTPLAYER * MERGEFORMAT } "has tape player" "does not have tape player"" "This child is a preteen" }
You could actually do a series of embedded IF statements for each article, but one of the tricky aspects is to not leave blank lines, and the approach I suggested can do it, though it gets pretty messy trying to make sure you put returns where you need them. Also note that you can’t simply paste in the statements from this post – Word needs to insert them as fields. Hope this makes it a little clearer, but on the other hand

Reply | Quote

could you make that example a bit more clear– I have NO idea what code you have written or where it is– I have used the built in features of word — to insert an if then else statement– I would REALLY like to use your idea, but dont know how to do it

Reply | Quote

I got sidetracked yesterday by other things, but this is a sample of what I meant by nested IF statements:
(When you view the attachment, you will need to highlight the “less”, right click and select “Toggle Field Codes”.)

Reply | Quote

that sounds like EXACTLY what I wanted– do not know how to embed one “if then else” into another–any help you can offer would be MUCH appreciated– right now I have a yes no field on each of the four questions– would like it to not print if the child was over a certain age (say 10) and print “this child Does(or this child does not) have a watch. I am off to teach a class and will return about 10 tonight (it is 5 something now) there is a DOB field to identify the ages.

Reply | Quote

Sorry – I should have been more specific. The trick is to embed the individual IF…THENs into another IF…THEN which uses a field code to display those fields if a teen is involved, and not display them otherwise. It’s a bit tricky to say, and you have to be sure all field codes are visible before you do it, but we’ve done very much the same thing where some people had to meet certain criteria and get one message, and another set get a different and more complex set based on their situation. Hope this make sense. If not, try attaching your Word merge document, and I’ll try to mock up what I have in mind.

Reply | Quote

Dear Wendell– yes, I was using if then else– and I can do that–but as I mentioned–it still leaves me with childrens letters saying that they do not have a watch– would rather not have that on a 2 year old letter. Dividing teen and childrens letters would have some merit–but , I want the families printed at the same time so they can be kept together (it seems to work best when group sponsors have the whole family.) (I am sorry that I did not mention that in the original post) What I want, is to have those four answers not appear at all on the kids letters. I may just have to go with a three response answer and have one of the possibilities be Not Applicable. I will have to make this decision in the next two days as there are already many forms to enter. Thanks for your response.

Reply | Quote

There are a couple of ways to approach this – one is to do separate letters for teens and younger children. The other is to use an IF … Then … Else … field in your word merge document, and put different text depending on whether they are a teen or preschooler. The second approach is a bit more complicated but lets you work with a single merge letter. I gues there is another way as well. You could construct the text of the Has/DoesNotHave section of your letter as a text string in a query and just merge that result to a single field in your merge document.

Reply | Quote