• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • Last Modified:

I want to use a calculated field in a word mail merge, but all the other information is from the table, how can I split it?

I want to use a calculated field in an access form in a mail merge. But, when it merges, it's blank. This is as I am getting the information from the table, not the similarly named calculation field. I need to link the calculated field to input information in the table to merge it to a word doc. Here's the scenario, we are a law firm. We have an access data base to calculate a "demand amount," lets say, $500.00. The statute this case falls under allows for TRIPLE damages. So, in an input form, our data entry people type...$500.00 for the [Demand amount]. This control is bound to the table, so the table field says, "$500.00." I also have a calculated FORM field called, [triple damages amount] which control states =[demand amount]*3. I want this field to place the calculated information into the TABLE for [triple damages], BUT I DO NOT KNOW HOW TO PROGRAM.

The purpose of this field is to create automated lawsuits as these are small nuisance suits under $5,000.00. We have hundreds of them, so the complaints (lawsuits) need to be automated. I created a mail merge for the complaint (which works fantastic, except for calculated fields, all these are blank)...but one of the fields must be the [triple damages amount] in the complaint. A mail merge only works off one table so I can't use multiple forms or queries. How can I get a calculated field to update a table when both controls are named identically.

I basically need to state:
When I input $500 in the form field [form!damages], multiply it by 3 ([form!damages]*3), and place the answer on this form field (form!triple damages)(=[form!damages]*3) but also, load this answer into a table field called triple damages ([table!triple damages]; how do I do this part, where is it entered, and what is the specific command?); once the table is updated, I can use my existing mail merge (which is 37 pages long with multiple fields (about 200 fields throughout the merge and input form, I'd prefer not to rewrite the merges if possible). Please help you guys/gals...and by the way, thank you all in advance for your assistance.
0
satzm
Asked:
satzm
  • 3
1 Solution
 
Stephen_PerrettCommented:
I think what you need to do is create a query based on the table which contains all the fields you need for your mail merge

2 create a calculated field in your query by placing the following

triple damages: [damages]*3

into a blank cell of the Field row when query is viewed in design view.

When you view the query in datasheet view you should see the field triple damages.

save your query with an appropriate name e.g.

qryTripleDamages

Base your mail merge on this query and you will find the triple damages field is available

Steve
0
 
satzmAuthor Commented:
Steve: Thank you, but this would necessitate that I rewrite my entire document which merges over 200 fields over dozens of pages for the lawsuit complaints and discovery materials. Is there any other way to do this, using the existing table? I spent days writing the silly thing and making sure all the merges worked properly as so many fields and text are interdependent. Thus, I would like to TRY to keep using the existing TABLE as the basis for the merge.
0
 
Stephen_PerrettCommented:
I wouldn't have thought that you would need to write a completely new Word document. Most (all but one of the fields will be identical to what you already have. I will do a quick test and get back to you.

Steve
0
 
Stephen_PerrettCommented:
It seems OK to me!

Try the following

1. First save a second copy of your mail merge Word Document using Save As

2. Close the document

3. Open the newly created  document

4. When prompted to use data source, click No.

5. Go through the mail merge setup a second time but this time choose the query instead.

All the fields should now be pointing to the query. All you have to do is to insert the calculated fields where you want them.

Steve

0
 
GreymanMSCCommented:
Some posibilities come to mind.

1. Create an update query which should be run prior to doing the merge to ensure current data.  The best time is probably after you update the table.

The query would be:

   Update [PutYourTableNameHere] set [triple damages]=3*[damages] where not [triple damages]=3*[damages];

2.  The place to run it would be in the Form_AfterUpdate() event.

  Private Sub Form_AfterUodate()
    Dim dB as DAO.Database
    Set dB = CurrentDB()
    dB.Execute "PutTheQuerryNameHere"
    '<-- although I would use something like this to cut out the middleman:
    ' dB.Execute _
        "Update [PutYourTableNameHere] set [tripple damages]=3*[damages] where [PutTheIDFieldHere]=" _
        & Me.[PutTheIDControlHere].Value & ";"
    ' -->
    dB.Close
    Set dB=Nothing
  End Sub

3. OR you could just use a calculated field in the merge document itself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now