The Bug of all Bugs.....[MSFT] Please?

[Bigus Di©kus]

Here is a major bug that I've noticed nobody else has posted about. I've done a
few quick searches and couldn't find anything at least.

In Word 2002, I create alot of templates. In those templates, I use alot of
"Text Form Fields". There are times that I need to reference a previous text
form field and the calculations yield unwanted results in the new Text Form
Field. I'm sure I'm not the only one, so if any one else has noticed a problem
with the calculations, let me know. I'll elaborate....

In Word (try any version), right click the Toolbar and click "Forms" to show the
Forms Toolbar.

On that toolbar, create a "Text Form Field"

Double-click the newly created "Text Form Field"

Change "Type" to "Calculation"

Type the Value: 10 * 5

NOTE the Bookmark name: "Text1"

Click OK, then note the Value that was displayed.....50

Add a couple of lines after your Text Form Field

Create a NEW "Text Form Field"

Double-Click the new Text Form Field

Change "Type" to "Calculation"

Type in the Value "Text1" after the = sign....this should reference the value in
Text1

Click OK and look at the Value that was displayed...... 5050

WHY!?!?!??!?!?!?!?!?

NOW....try that again, but replace The Value in Text1 (10 * 5) with any number
with a decimal point that does not equal 0 (i.e. 1.1 * 2.2, or 6.35789 *
5.57885).

WHY?!?!?!?!??!?! All I'm trying to do in my examples here is call the value of
a Field....I want to manipulate it further, but I can't because the results are
all screwed up. They are SIMPLE calls...there is nothing out of the ordinary
here.

If there is a [MSFT] representative that can pass this on to the developers
please do, because I'm not paying MS $35 to point out a problem....they should
be paying me for noticing it.

 

[macropod]

This behaviour is well documented (see
http://support.microsoft.com/kb/110656/en-us) and can easily be solved by
using a formula field to do the calculation, rather than a form field. The
MSKB article suggests other approaches also.

For the example you cited, a simple REF field would be enough to replicate
the form field's result.

Cheers

 

[Bigus Di©kus]

<< While I was at work, macropod spurted out:

This behaviour is well documented (see
http://support.microsoft.com/kb/110656/en-us) and can easily be
solved by using a formula field to do the calculation, rather than a
form field. The MSKB article suggests other approaches also.

For the example you cited, a simple REF field would be enough to
replicate the form field's result.

Cheers

Thanks, I'll give it a look over.

As for the REF field, yes, it would work for the example....but what I'm trying
to do in my documents is take the referenced field and multiply it by another
number, or in many cases, by another referenced field....you can't do that to a
REF field (that I know of).

Thanks

 

[Bigus Di©kus]

<< While I was at work, Bigus Di©kus spurted out:
<<------------------------------------------>>
I just realized this issue has been around since Word 6.0. WTF!!!! Does MS not
employ ANYONE that can figure out how to fix this? Wholly carp, it's not that
difficult!! Is It?

 

[Bigus Di©kus]

<< While I was at work, macropod spurted out:

This behaviour is well documented (see
http://support.microsoft.com/kb/110656/en-us) and can easily be
solved by using a formula field to do the calculation, rather than a
form field. The MSKB article suggests other approaches also.

For the example you cited, a simple REF field would be enough to
replicate the form field's result.

Cheers

 

[Bigus Di©kus]

<< While I was at work, macropod spurted out:

This behaviour is well documented (see
http://support.microsoft.com/kb/110656/en-us) and can easily be
solved by using a formula field to do the calculation, rather than a
form field. The MSKB article suggests other approaches also.

For the example you cited, a simple REF field would be enough to
replicate the form field's result.

Cheers

Just so you know, that article applied to a few of the OLDER versions of
Word.... 6.0 and 95. It had nothing I could use. I did the search under 2000,
2002, XP, and 2003 and there was nothing.

I'll just have to start doing the calcs in Excel and I'm porting the table into
Word.....but it really sucks that I have to do that, because I have these form
fields on page 15 and I make a reference to them on page 65 of my document.

I'll check my Office 2003 at home for this issue and see if it was fixed in that
version.

Thanks for your help.

 

[macropod]

Re: "I did the search under 2000, 2002, XP, and 2003 and there was nothing."
You are joking, aren't you? There are most definitely articles on later
versions, all saying essentially the same thing. I gave you the Word 6
reference just to show how long this has been documented for - despite your
protestations to the contrary.

Since the example you gave concerns a form field calculation, which doesn't
allow user input, it must either be hard-coded, or is obtaining it's data
from one or more other fields. Instead of using a form field for the
calculation, you could us a formula field - no need to use Excel at all.

Cheers

 

[macropod]

FYI, Word 2003 has fixed the problem.
If you want the document to be reliable when used with older versions, don't
rely on the Word 2003 solution.

Just so you know, my longest calc has 30 variables/references to other

fields
That's OK - without wanting to get into a 'Mine's bigger that your's
contest' - especially given your moniker - consider:
{QUOTE
{ASK pv "What is the present loan/investment value?"}
{ASK fv "What is the anticipated future loan/investment value?"}
{ASK interest "What is the annual rate of interest, expressed as a number
(not as a percentage)?"}
{SET rate{=interest/100/12 \# 0.000000000000}}
{ASK pmt "What is the monthly instalment?"}
{ASK type "When are payments made? 0 = at the end of the period, 1 = at the
start of the period." \d 0}

{SET l2l 0.301029995663981}
{SET l3l 0.477121254719662}
{SET l5l 0.698970004336019}
{SET l7l 0.845098040014257}
{SET l11l 0.0413926851582251}
{SET l13l 0.113943352306837}
{SET l17l 0.230448921378274}
{SET l19l 0.278753600952829}

{SET a{=abs(1+(pv+pmt*type)/pmt*rate)}}
{SET
b{=9-(a<10^9)-(a<10^8)-(a<10^7)-(a<10^6)-(a<10^5)-(a<10^4)-(a<10^3)-(a<10^2)
-(a<10^1)-(a<10^0)-(a<10^-1)-(a<10^-2)-(a<10^-3)-(a<10^-4)-(a<10^-5)-(a<10^-
6)-(a<10^-7)-(a<10^-8)}}
{SET c{=int(a/10^b)+mod(a,10^b)/10^b}}
{SET
d{=(c<1.05)*0+(c>=1.05)*(c<1.2)*l11l+(c>=1.2)*(c<1.5)*l13l+(c>=1.5)*(c<1.8)*
l17l+(c>=1.8)*(c<1.95)*l19l+(c>=1.95)*(c<2.5)*l2l+(c>=2.5)*(c<3.5)*l3l+(c>=3
..5)*(c<4.5)*l2l*2+(c>=4.5)*(c<5.5)*l50l+(c>=5.5)*(c<6.5)*(l2l+l31)+(c>=6.5)*
(c<7.5)*l7l+(c>=7.5)*(c<8.5)*l2l*3+(c>=8.5)*l3l*2}}
{SET e{=a-10^(b+d)}}
{SET
f{=b+d+0.434294481903251*((e/10^(b+d))-(e/10^(b+d))^2/2+(e/10^(b+d))^3/3-(e/
10^(b+d))^4/4+(e/10^(b+d))^5/5-(e/10^(b+d))^6/6+(e/10^(b+d))^7/7-(e/10^(b+d)
)^8/8+(e/10^(b+d))^9/9-(e/10^(b+d))^10/10)}}

{SET a{=abs(1+(fv+pmt*type)/pmt*rate)}}
{SET
b{=9-(a<10^9)-(a<10^8)-(a<10^7)-(a<10^6)-(a<10^5)-(a<10^4)-(a<10^3)-(a<10^2)
-(a<10^1)-(a<10^0)-(a<10^-1)-(a<10^-2)-(a<10^-3)-(a<10^-4)-(a<10^-5)-(a<10^-
6)-(a<10^-7)-(a<10^-8)}}
{SET c{=int(a/10^b)+mod(a,10^b)/10^b}}
{SET
d{=(c<1.05)*0+(c>=1.05)*(c<1.2)*l11l+(c>=1.2)*(c<1.5)*l13l+(c>=1.5)*(c<1.8)*
l17l+(c>=1.8)*(c<1.95)*l19l+(c>=1.95)*(c<2.5)*l2l+(c>=2.5)*(c<3.5)*l3l+(c>=3
..5)*(c<4.5)*l2l*2+(c>=4.5)*(c<5.5)*l50l+(c>=5.5)*(c<6.5)*(l2l+l31)+(c>=6.5)*
(c<7.5)*l7l+(c>=7.5)*(c<8.5)*l2l*3+(c>=8.5)*l3l*2}}
{SET e{=a-10^(b+d)}}
{SET
g{=b+d+0.434294481903251*((e/10^(b+d))-(e/10^(b+d))^2/2+(e/10^(b+d))^3/3-(e/
10^(b+d))^4/4+(e/10^(b+d))^5/5-(e/10^(b+d))^6/6+(e/10^(b+d))^7/7-(e/10^(b+d)
)^8/8+(e/10^(b+d))^9/9-(e/10^(b+d))^10/10)}}

{SET a{=abs(1+rate)}}
{SET
b{=9-(a<10^9)-(a<10^8)-(a<10^7)-(a<10^6)-(a<10^5)-(a<10^4)-(a<10^3)-(a<10^2)
-(a<10^1)-(a<10^0)-(a<10^-1)-(a<10^-2)-(a<10^-3)-(a<10^-4)-(a<10^-5)-(a<10^-
6)-(a<10^-7)-(a<10^-8)}}
{SET c{=int(a/10^b)+mod(a,10^b)/10^b}}
{SET
d{=(c<1.05)*0+(c>=1.05)*(c<1.2)*l11l+(c>=1.2)*(c<1.5)*l13l+(c>=1.5)*(c<1.8)*
l17l+(c>=1.8)*(c<1.95)*l19l+(c>=1.95)*(c<2.5)*l2l+(c>=2.5)*(c<3.5)*l3l+(c>=3
..5)*(c<4.5)*l2l*2+(c>=4.5)*(c<5.5)*l50l+(c>=5.5)*(c<6.5)*(l2l+l31)+(c>=6.5)*
(c<7.5)*l7l+(c>=7.5)*(c<8.5)*l2l*3+(c>=8.5)*l3l*2}}
{SET e{=a-10^(b+d)}}
{SET
h{=b+d+0.434294481903251*((e/10^(b+d))-(e/10^(b+d))^2/2+(e/10^(b+d))^3/3-(e/
10^(b+d))^4/4+(e/10^(b+d))^5/5-(e/10^(b+d))^6/6+(e/10^(b+d))^7/7-(e/10^(b+d)
)^8/8+(e/10^(b+d))^9/9-(e/10^(b+d))^10/10)}}

{SET nper{=-if(rate=0,(pv+fv)/pmt,(f-g)/h)}}"A loan or investment with a
present value of {pv \# $+,0.00;$ ,0.00;$0.00}, an anticipated future value
of {fv \# $+,0.00;$ ,0.00;$0.00}, attracting an interest rate of
{interest}%pa, will last for {nper \# +,0.0000; ,0.0000;0.0000} months if
payments of {pmt \# $+,0.00;$ ,0.00;$0.00} are made at the {IF{=type}= 1
"start" "end"} of each month."}

I think you'll find that 61 fields embedded in a single calculation. I don't
think 30 would have fazed me.

Cheers

 

[Bigus Di©kus]

<< While I was at work, macropod spurted out:

If you want the document to be reliable when used with older
versions, don't rely on the Word 2003 solution.

Good piece of advice...I try not to rely on anything I have not personally
tested. Thanks

Just so you know, my longest calc has 30 variables/references to
other

fields
That's OK - without wanting to get into a 'Mine's bigger that your's
contest' - especially given your moniker - consider:

[snip]

I think you'll find that 61 fields embedded in a single calculation.
I don't think 30 would have fazed me.

You sure went through an awful lot for someone not wanting to get into a 'Mine's
bigger that your's
contest'. Ok...yours was bigger, but that's ok. I've got you beat in other
areas.

I really wasn't pointing you out, I've just had too many morons that try to
figure out the problem by trying to get the calculation to work. It just
doesn't work like that. That's why I displayed the basic portion of the
problem.

Believe me, I'm going to give your method a try. But for now, I can't go back
and revise all my documents in a few hours spread out over the week. As soon as
work slow a little, I'll be able to take an entire week to focus on the issue.

Cheers

Thanks macro...

 

[zx6roo]

I'm having a similar problem but my calculation is a little easier.

In excel I have =+B8*D3, I put this into Word but to make the forms
protection owrk I had to convert it to a table. So in the forms calc I wrote
that (in cell D8) and it showed me 0.00 (number format and bookmark are left
blank). When I put a value in B8 D8 does not update no matter what I do.

I have tried changing it to {=+B9 *D$3}, =sum(+B9 *D$3) and various other
things but nothing is working. However if I put in a simple calc like
=B9+D3 it works fine.

Also the other issue is that in B22 I have teh calc =sum(above) and set it
to update calc on exit and it won't.

Can anyone help with these two issues please?
Thanks.

 

[macropod]

Hi zx6roo,

In the cell where you want the calculation to appear:
.. press Ctrl-F9 to create a pair of field braces (i.e. '{ }')
.. between the field braces, enter '=B9*D3', such that you get {=B9*D3}
.. after the formula, enter an appropriate numeric picture switch, eg:
{=B9*D3 \# $,0.00}.

With your formfield(s), in B9 and/or D3, make sure their properties are set
to 'calculate on exit'

Protect the document for forms and it should work.

See Word's help file for more details on the use of field switches.

Cheers