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?

Posted on 2005-04-26
Last Modified: 2012-06-27
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.
Question by:satzm
    LVL 11

    Expert Comment

    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.


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


    Author Comment

    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.
    LVL 11

    Accepted Solution

    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.

    LVL 11

    Expert Comment

    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.


    LVL 16

    Expert Comment

    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 & ";"
        ' -->
        Set dB=Nothing
      End Sub

    3. OR you could just use a calculated field in the merge document itself.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now