Form fields that calculate (2002)

Topic

New Reply

I’m designing a form in Word 2000 or later that contains several numeric fields and several fields that perform calculations based on the numeric fields. I cannot find any information on the syntax for calculations in form fields. Also, I would be interested in a formula that gives different results depending on the results of calculations in other fields. Does this require writing code?

Thanks in advance, Karen

Reply | Quote

Replies

Hi Karen:
Calculations start with an = sign, so the general syntax is:
{= bookmark operator bookmark} where the curly braces are inserted with Ctrl+F9, not typed. In addition to normal +, -, etc. there are functions like SUM (above). Press F1 for the Help menu & search for formula & you will be able to find all the operators & functions.
Cheers,

Reply | Quote

Each form field has a bookmark; you can refer to this bookmark in a formula. To make sure the formula is updated automatically, tick “Calculate on Exit” in the properties of the form field.

Formulas are also fields, but not form fields; you insert them using Insert | Field…, or by typing Ctrl+F9 to insert field brackets { } (don’t type the brackets yourself) and inserting the field code between them.

Say that you have two form fields that are set to contain numbers. The associated bookmarks are Num1 and Num2. The following simple formula displays the product of the numbers (again, don’t type the brackets yourself):

{ = { Num1 } * { Num2 } }

If you want to perform conditional calculations, use the IF field. Example: if Num1 > 10, you want to add Num2 to Num1, otherwise, you want to subtract Num2 from Num1. Here is the field code:

{ IF { Num1 } > 10 { = { Num1 } + { Num2 } } { = { Num1 } – { Num2 } } }

See the online help for information about the IF field and about the = (Formula) field. The latter has a list of functions you can use in formulas, such as SUM and MAX. If you want to see really advanced use of formulas and fields, download macropod’s Date Calculations in Word.

Reply | Quote

If you have a form with multiple fields (bookmarks), how do you see the name of the bookmark? Is it possible to fill in one field and have that value appear elsewhere in the document? If so is it simply a formula of =value(bookmarkname) or similar?

Reply | Quote

I’m not a fields expert, but this is my understanding:

> If you have a form with multiple fields (bookmarks), how do you see the name of the bookmark?

It is listed in the form field’s properties dialog.

> Is it possible to fill in one field and have that value appear elsewhere in the document? If so is it
> simply a formula of =value(bookmarkname) or similar?

You generally use a REF field. Try Insert>Field…>Ref and check out all your options.

Reply | Quote

I’m not a fields expert, but this is my understanding:

> If you have a form with multiple fields (bookmarks), how do you see the name of the bookmark?

It is listed in the form field’s properties dialog.

> Is it possible to fill in one field and have that value appear elsewhere in the document? If so is it
> simply a formula of =value(bookmarkname) or similar?

You generally use a REF field. Try Insert>Field…>Ref and check out all your options.

Reply | Quote

Hi Don,

You can see bookmark names via Insert|Bookmark, but that doesn’t show you their values. You can see any bookmark’s value by using a REF field to display it, as in:
{REF BkMrk} or, more simply, {BkMrk}
where the field braces ‘{}’ are created as a pair via Ctrl-F9. After typing the bookmark name, press F9 to display the field’s results.

Another way of listing bookmarks and their values is to run a macro like the following, which generates a list of all bookmarks at the end of the active document, and displays their contents:

Sub ListBkMrks()
Dim oBmk As Bookmark, oBkMrk
If ActiveDocument.Bookmarks.Count > 0 Then
For Each oBmk In ActiveDocument.Bookmarks
With Selection
.EndKey Unit:=wdStory
.InsertAfter vbCrLf & oBmk.Name & ” ”
.EndKey Unit:=wdStory
oBkMrk = ActiveDocument.Fields.Add(Range:=Selection.Range, Text:=oBmk.Name, PreserveFormatting:=False)
End With
Next oBmk
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod:
This macro doesn’t work in Word 2000, as I get a message that “Method or data member not found” for .Name. Is there a way around that for Word 2000?
Thanks,

Reply | Quote

Oops,

Wrong value in Dim: should have been ‘Bookmark’, not ‘Field’

Original post updated.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Oops,

Wrong value in Dim: should have been ‘Bookmark’, not ‘Field’

Original post updated.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod:
This macro doesn’t work in Word 2000, as I get a message that “Method or data member not found” for .Name. Is there a way around that for Word 2000?
Thanks,

Reply | Quote

Hi Don,

You can see bookmark names via Insert|Bookmark, but that doesn’t show you their values. You can see any bookmark’s value by using a REF field to display it, as in:
{REF BkMrk} or, more simply, {BkMrk}
where the field braces ‘{}’ are created as a pair via Ctrl-F9. After typing the bookmark name, press F9 to display the field’s results.

Another way of listing bookmarks and their values is to run a macro like the following, which generates a list of all bookmarks at the end of the active document, and displays their contents:

Sub ListBkMrks()
Dim oBmk As Bookmark, oBkMrk
If ActiveDocument.Bookmarks.Count > 0 Then
For Each oBmk In ActiveDocument.Bookmarks
With Selection
.EndKey Unit:=wdStory
.InsertAfter vbCrLf & oBmk.Name & ” ”
.EndKey Unit:=wdStory
oBkMrk = ActiveDocument.Fields.Add(Range:=Selection.Range, Text:=oBmk.Name, PreserveFormatting:=False)
End With
Next oBmk
End If
End Sub

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

If you have a form with multiple fields (bookmarks), how do you see the name of the bookmark? Is it possible to fill in one field and have that value appear elsewhere in the document? If so is it simply a formula of =value(bookmarkname) or similar?

Reply | Quote

Hi Hans,

It’s even simpler than that. Try:
{=Num1*Num2}
That’s right, you don’t need to use REF fields to refer to the bookmarks used in a calculation!

Cheers
PS: For what it’s worth, I’m attaching a draft of a field maths document, similar to the date calc one.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Wow! That’s impressive! I’m sure many people will find it useful.

Reply | Quote

Wow! That’s impressive! I’m sure many people will find it useful.

Reply | Quote

I agree with Hans. It is impressive. Since it’s a draft, I would suggest version numbers (like you did with the DateCalc.doc). I would also define what “ve” stands for, although the context in your attachment makes it obvious.

Reply | Quote

I agree with Hans. It is impressive. Since it’s a draft, I would suggest version numbers (like you did with the DateCalc.doc). I would also define what “ve” stands for, although the context in your attachment makes it obvious.

Reply | Quote

Hi Macropod,

I think too that this is a very nice piece of work.
Provided your Windows is configured to use “,” for grouping on thousands and “.” for decimals, that is. But in Holland it’s the other way around, so I get syntax errors all over the place in my Dutch version of Word 2003.
Is there a simpel way to make the formulae behave correctly indepent of this Windows configuration? Or is that only possible by replacing every “,” with “.” an vice versa?

Reply | Quote

Hi Jan,

I don’t think there’s any way to get around having to use the settings-dependant thousands/decimals separator issue. Although I could leave out the thousands separator (and examples on how to use them in field switches), end-users would still have to change the decimal separator.

Cheers???

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I was afraid of that. Thanks anyway for a great post!

Reply | Quote

I was afraid of that. Thanks anyway for a great post!

Reply | Quote

Hi Jan,

I don’t think there’s any way to get around having to use the settings-dependant thousands/decimals separator issue. Although I could leave out the thousands separator (and examples on how to use them in field switches), end-users would still have to change the decimal separator.

Cheers???

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Macropod,

I think too that this is a very nice piece of work.
Provided your Windows is configured to use “,” for grouping on thousands and “.” for decimals, that is. But in Holland it’s the other way around, so I get syntax errors all over the place in my Dutch version of Word 2003.
Is there a simpel way to make the formulae behave correctly indepent of this Windows configuration? Or is that only possible by replacing every “,” with “.” an vice versa?

Reply | Quote

Hi Hans,

It’s even simpler than that. Try:
{=Num1*Num2}
That’s right, you don’t need to use REF fields to refer to the bookmarks used in a calculation!

Cheers
PS: For what it’s worth, I’m attaching a draft of a field maths document, similar to the date calc one.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Each form field has a bookmark; you can refer to this bookmark in a formula. To make sure the formula is updated automatically, tick “Calculate on Exit” in the properties of the form field.

Formulas are also fields, but not form fields; you insert them using Insert | Field…, or by typing Ctrl+F9 to insert field brackets { } (don’t type the brackets yourself) and inserting the field code between them.

Say that you have two form fields that are set to contain numbers. The associated bookmarks are Num1 and Num2. The following simple formula displays the product of the numbers (again, don’t type the brackets yourself):

{ = { Num1 } * { Num2 } }

If you want to perform conditional calculations, use the IF field. Example: if Num1 > 10, you want to add Num2 to Num1, otherwise, you want to subtract Num2 from Num1. Here is the field code:

{ IF { Num1 } > 10 { = { Num1 } + { Num2 } } { = { Num1 } – { Num2 } } }

See the online help for information about the IF field and about the = (Formula) field. The latter has a list of functions you can use in formulas, such as SUM and MAX. If you want to see really advanced use of formulas and fields, download macropod’s Date Calculations in Word.

Reply | Quote

I’m new to this posting stuff. Is this the correct way to add more to the thread? I just clicked Respond to this Post.

I found an excellent book on form fields. It’s Using MS Office Word 2003 by Bill Camarda. It goes into a lot of detail and has good examples

I did some debugging and I think my problem is that one of the fields used in the calculation is a dropdown with choices 1, 2, 3, 4, and 5. I made the first choice in the dropdown ‘Choose one’ so that would be the default and make sure someone didn’t accidentally skip that question. I think the reason my formula wouldn’t work is that I put TEXT in the choices instead of just numbers.

Thanks for all the input. I learned a lot from your responses.
Karen

Reply | Quote

Hi Karen:
When the new information relates to the original question, as yours did, responding in the same thread is appropriate, even preferred. And in case we neglected to say so before, “Welcome to the Lounge”.

Reply | Quote

Hi Karen:
When the new information relates to the original question, as yours did, responding in the same thread is appropriate, even preferred. And in case we neglected to say so before, “Welcome to the Lounge”.

Reply | Quote

I’m new to this posting stuff. Is this the correct way to add more to the thread? I just clicked Respond to this Post.

I found an excellent book on form fields. It’s Using MS Office Word 2003 by Bill Camarda. It goes into a lot of detail and has good examples

I did some debugging and I think my problem is that one of the fields used in the calculation is a dropdown with choices 1, 2, 3, 4, and 5. I made the first choice in the dropdown ‘Choose one’ so that would be the default and make sure someone didn’t accidentally skip that question. I think the reason my formula wouldn’t work is that I put TEXT in the choices instead of just numbers.

Thanks for all the input. I learned a lot from your responses.
Karen

Reply | Quote

I am still having problems with the form fields:
I want to have a formula like this: {=JKRank * JKScore*100}

JKRank is a percent.
JKScore is a number ranging from 0 to 5 chosen from a dropdown.

The formula works as long as I do not include JKScore
It does not work when JKScore is included in any way in the formula, leading me to believe that you cannot put dropdown form fields in a formula.

Right?

I have also noticed that the formula typed into a calculation form field contains no {} but a formula entered manually with CTRL F9 has the {} around each bookmark cause that’s the way I entered it. (The latter formula works but of course it doesn’t calculate automatically.) Does this explain why the calculation form field doesn’t work?

As a test, I manually entered with CTRL F9 the formula {=percent*score} and it did not work, whereas {={percent}*{score} } did work.

Thanks, Karen

Reply | Quote

Hi Karen:
So far as I know, dropdowns don’t work in a formula field. However, a REF field that references the bookmarked dropdown does. So:

{ =.10 * { REF Dropdown1 }*100 } will work, where the dropdown is somewhere else in your document. Also,

{ =.10 * { Dropdown1 }*100 } also works because you don’t have to enter the word REF. The syntax { SomeName } is assumed to be a REF field for the bookmark “SomeName”.

{ =.10 * { FORMDROPDOWN }*100 } will not work. See attached.
Cheers,

Reply | Quote

Thanks, Phil.

That’s the way I did it. There’s a dropdown where the user chooses a number from 0 to 5. Later in the document there’s a calculation form field that uses the bookmark from the dropdown field.

But it doesn’t work.
Karen

Reply | Quote

Phil
Ooops, guess I should have checked out the attachment before I replied to your post.

I opened the attachment and I don’t think the formulas are calculation form fields; they appear to be just regular fields with a formula entered manually. Right?

Karen

Reply | Quote

Hi Karen:
Yes, I entered the calculation fields manually. If you are using formfields to perform calculations & have multiple calculations (using the results of one calculated formfield in another calculated formfield), there is a bug in Word. I can’t find the article right now (too late to stay up). You can search the Knowledge Base for formfields and calculation.
Hope this helps,

Reply | Quote

Hi Karen:
Yes, I entered the calculation fields manually. If you are using formfields to perform calculations & have multiple calculations (using the results of one calculated formfield in another calculated formfield), there is a bug in Word. I can’t find the article right now (too late to stay up). You can search the Knowledge Base for formfields and calculation.
Hope this helps,

Reply | Quote

Phil
Ooops, guess I should have checked out the attachment before I replied to your post.

I opened the attachment and I don’t think the formulas are calculation form fields; they appear to be just regular fields with a formula entered manually. Right?

Karen

Reply | Quote

Thanks, Phil.

That’s the way I did it. There’s a dropdown where the user chooses a number from 0 to 5. Later in the document there’s a calculation form field that uses the bookmark from the dropdown field.

But it doesn’t work.
Karen

Reply | Quote

Hi Karen:
So far as I know, dropdowns don’t work in a formula field. However, a REF field that references the bookmarked dropdown does. So:

{ =.10 * { REF Dropdown1 }*100 } will work, where the dropdown is somewhere else in your document. Also,

{ =.10 * { Dropdown1 }*100 } also works because you don’t have to enter the word REF. The syntax { SomeName } is assumed to be a REF field for the bookmark “SomeName”.

{ =.10 * { FORMDROPDOWN }*100 } will not work. See attached.
Cheers,

Reply | Quote

I am still having problems with the form fields:
I want to have a formula like this: {=JKRank * JKScore*100}

JKRank is a percent.
JKScore is a number ranging from 0 to 5 chosen from a dropdown.

The formula works as long as I do not include JKScore
It does not work when JKScore is included in any way in the formula, leading me to believe that you cannot put dropdown form fields in a formula.

Right?

I have also noticed that the formula typed into a calculation form field contains no {} but a formula entered manually with CTRL F9 has the {} around each bookmark cause that’s the way I entered it. (The latter formula works but of course it doesn’t calculate automatically.) Does this explain why the calculation form field doesn’t work?

As a test, I manually entered with CTRL F9 the formula {=percent*score} and it did not work, whereas {={percent}*{score} } did work.

Thanks, Karen

Reply | Quote