Formulas using merged and form field data

[Smhall]

I have a form that merges data from an Access database. It also has form
fields to be filled in by the user. How can I set up a formula that uses
both sources. Example: Current Pay comes over from access, Increase Amount
comes from the user. I want to calculate the new pay and percentage increase.

 

[Peter Jamieson]

As long as you have worked out how to merge a document with form fields, the
thing to recognise is that each form field has a bookmark name that you can
use in a calculation. So for example, if you just need to add Current Pay
and Increase Amount, and Increase Amount was in a form field with bookmark
bkmIncreaseAmount, you would need something like

{ ={ MERGEFIELD "Current Pay" }+{ REF bkmIncreaseAmount } }

where all the {} are the special field braces you can insert using ctrl-F9

Peter Jamieson

 

[Smhall]

I have tried doing a simple calculation, but when I merge the form (using a
macro I found on this site!), I get errors instead of a result. Without
doing the merge, the formulas work. As soon as I run the merge macro, the
formulas stop working.

 

[Doug Robbins - Word MVP]

What was the simple calculation.

It is probably failing because it is making use of the bookmark names
assigned to the formfields in the mail merge main document. Those bookmarks
will not be present in the documents created by executing the mailmerge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Yes, probably my mistake...

Peter Jamieson

What was the simple calculation.

It is probably failing because it is making use of the bookmark names
assigned to the formfields in the mail merge main document. Those
bookmarks will not be present in the documents created by executing the
mailmerge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Smhall]

If the bookmarks aren't in the new document created by the mail merge, does
that mean I can't use them in a formula. The formula is { ={ MERGEFIELD
expr1 }*{ formfield MeritInc} }. Expr1 is the current pay amount from Access
and MeritInc is the percentage increase a supervisor wants to give.

 

[Peter Jamieson]

For starters, you should be using { REF MeritInc }, not { formfield
MeritInc }, where MeritInc is the name of the bookmark that you set in the
form field's properties.

You can do calculations using REF fields in a mailmerge main document
because the calculations are done /during/ the merge, /before/ the bookmarks
on which they rely are removed. However, when form fields are involved you
have to jump through extra hoops to do the merge, so what happens depends on
precisely how you are doing that.

Peter Jamieson