Conditional letters

[Kaylen]

Hello, I am working on building a letter templates database because there are
so many templates we use daily. I have recently learned how to generate
letters incoporating fields to personalize address, etc. Now my problem is
that I have many conditional pragapraghs. A letter is to include a certain
paragrpah if a conditon is met or not met. For example, if a balance is
overdue more than 90 days, we need to include a collection paragraph, if not
then that paragragh will not be included. Tha is just an example of one, I
have multiple conditonal paragraphs within one single letter and there can be
20 versions of that same letter because of many possibile conditons. I want
to have one template that allows the users to choose the conditions and a
letter will be generated from those conditions. How would I go about
programing or creating a letter template that would allow this? Any
suggestion would be so grateful to me!!

 

[Clifford Bass]

Hi Kaylen,

Untried: Include the values of the conditions as invisible controls,
maybe checkboxes, in your detail section. For the conditional paragraphs,
place each of them in separate text boxes. Set the can shrink property of
each to yes. In the detail format event check the values of each of those
conditions and set the contents of the corresponding paragraph's text box to
the appropriate text or to null.

Alternatively, you could use a primary Word document and an Access
query as the data source. Again, in that query, include the conditions.
Then in Word you can test those and decide whether or not to include
particular paragraphs. I think I would go this route because of the superior
editing, formatting and document flow abilities. In fact I have done it that
way. Converted about eight to ten variations of a merge letter into one!
And it is easier for the users to edit the various paragraphs than if they
were part of an Access report. Just make sure they edit a copy in case they
mess up.

Hope this helps,

Clifford Bass

 

[Kaylen]

I like the Word document merge method. But it sounds complicated. Where
would the Words letter templates be kept? On Access or in the user's hard
drive? How would I go about making the query merge with a Word template? Can
you give me more detailed instructions? Sorry, I'm a beginner with Access.

 

[Clifford Bass]

Hi Kaylen,

The "templates", not really a template in the true sense, could be in
either place. I would keep my own master copies somewhere that is separate
and safe from others.

What version of MS Office are you using? How familiar are you with
merging in Word? The steps of how to do it changed a bit between 2003 and
2007, but not the concepts. So I will need to know which so I can direct you
correctly.

Clifford Bass

 

[Clifford Bass]

Hi Kaylen,

Just reread your first question. The would not be stored in Access.
Just keep the documents where ever Word documents are normally kept.

Clifford Bass

 

[Kaylen]

Thank you Clifford, for answering my questions. I am using Office 2003. What
I want to do is to create a template database for users to generate letters.
There are many version of a single letter and there are many letters. I want
to create a single database or tool that allow users to enter in the
necessary info and generate the letter version they want. Therefore, I would
not want to keep the templates in one place, and the Access generation dtb in
another place. That would be too complicated for the users. I would like to
keep everything in one single database and allow users to enter info in a
form or something and generate a version of a selected letter. Hope you can
lead me to the right direction.

 

[Clifford Bass]

Hi Kaylen,

I will get back to you on this. Of course, if someone else has
ideas/thoughts, they should feel free to express them.

Clifford Bass

 

[Clifford Bass]

Hi Kaylen,

In that case here are my thoughts on the direction I probably would go.
You can decide to what extent you wish to do the same. Most of this is
thinking off the top of my head and definitely would need fine-tuning.

I still would lean towards Access to hold the data and Word to do the
merging. Word because it is far superior in dealing with issues such as
formatting and printing of letters that Access's report tool. Technically
you can store the documents inside a table in Access. I don't think I would
go that route. But rather would store copies of both the database and the
word documents locally on the users' machines. I am assuming that you are
using some kind of backend database--Access or something else. In the
database, I might have a table that will be used to hold the pertinent data
for the letters (i.e. customer ID, switches for which paragraphs need
including, etc.). I probably would make it one table that works for all of
the letters, with the letters only looking at the data appropriate to each
letter. If you have a back end that incorporates user-level table/view
security you could use a table and a view in that database instead of in the
front end. If this is the case and you want more information, let me know.
Otherwise, my choice of storing the table in the front end is so that one
user's entries do not get mixed up with some other user's entries. You will
need to create a query to pull together the customer information (name,
address, account number, etc.) from the back end and the data from the table
on the front end. Then you could have the form (or maybe different forms for
different letters) that asks the user for the letter specifications and
stores them in the table. And a button to open up the merge letter in Word.
And finally, assuming that you need to record the sending of the letter
somewhere in the main database, a button to record the sending of the letter.
I like to make that part manual rather than automatic in case the letter
fails to print for some reason, or the user sees a problem and has to close
out and change the options.

In Word, you can set up your letters as follows. Tools menu, Letters
and Mailings, Mail Merge.... This will start the mail merge wizard. When
you get to step three choose "Use an existing list" and then "Browse...".
Choose your Access front end database and then the query. In step 4, when it
comes to the conditional paragraphs, you can use the "Insert Word Field" in
the Mail Merge tool bar. Choose the "If...Then...Else..." item. Choose the
appropriate field, comparison, and enter what you want to compare it to (-1
for true or 0 for false if a boolean field). And add the paragraph to the
appropriate text box. For more help on Word merging if you need it do a
search for "mail merge" in Word's help.

Hope that helps,

Clifford Bass