Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / July 2005

Tip: Looking for answers? Try searching our database.

Calculated fields are doubled.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Anne Troy - 22 Jul 2005 19:52 GMT
Word 2000 & 2003. We've got form fields with bookmark names, like Fee and
Expenses. Even a simple calc like =Gross-(Fee+Expenses+TotalDeductions)

I am sure I've seen this before working a helpdesk, but I don't remember the
answer. :(

If you like, you can have a look at the file here:
http://www.myexpertsonline.com/downloads/calcs.doc

There is no code in the document.

Thanks very much!
*******************
~Anne Troy

www.OfficeArticles.com
Andrea Jones - 22 Jul 2005 21:41 GMT
It's because when the calculation looks at a form field like your 'Total
Deductions' it runs the contents of that form field again thus doubling its
result.  You can get the correct answer in your form by doing something like
=Gross-(Gross*Contingency)-CostsAdvanced-Postage-Copies-Telephone-Administrative-FinanceCharges-Amount1-Amount2-Amount3-Amount4.

Personally I would use a macro that runs as you exit each form field and
recalculates everything:

Sub calcmacro()
Dim x As Double
Dim y As Double
Dim tot As Double
x=0
y=0
tot=0
x=Val(ActiveDocument.FormFields("Val1").Result)
y=Val(ActiveDocument.FormFields("Val2").Result)
tot=x+y
ActiveDocument.FormFields("Total").Result=tot
End Sub

The above macro will total two form fields bookmarked Val1 and Val2 and put
the answer in the form field bookmarked Total.  Set the calcmacro to run on
exiting every form field.  The form fields must be of a number or text type
(the Val function will make it work even on text fields but currency doesn't
work).  The Val part also deals with any rounding problems you may get from
adding taxes so your total is the expected answer.

Andrea Jones
http://www.allaboutoffice.co.uk
http://www.stratatraining.co.uk
http://www.allaboutclait.com

> Word 2000 & 2003. We've got form fields with bookmark names, like Fee and
> Expenses. Even a simple calc like =Gross-(Fee+Expenses+TotalDeductions)
[quoted text clipped - 12 lines]
>
> www.OfficeArticles.com
Anne Troy - 23 Jul 2005 00:51 GMT
Thanks, Andrea. I will make this suggestion to the person that asked. :)
*******************
~Anne Troy

www.OfficeArticles.com

> It's because when the calculation looks at a form field like your 'Total
> Deductions' it runs the contents of that form field again thus doubling its
> result.  You can get the correct answer in your form by doing something like

=Gross-(Gross*Contingency)-CostsAdvanced-Postage-Copies-Telephone-Administra
tive-FinanceCharges-Amount1-Amount2-Amount3-Amount4.

> Personally I would use a macro that runs as you exit each form field and
> recalculates everything:
[quoted text clipped - 40 lines]
> >
> > www.OfficeArticles.com
Doug Robbins - 22 Jul 2005 22:02 GMT
In a formfield calculation, you cannot use a formfield that contains the
result of another calculation.

Signature

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

> Word 2000 & 2003. We've got form fields with bookmark names, like Fee and
> Expenses. Even a simple calc like =Gross-(Fee+Expenses+TotalDeductions)
[quoted text clipped - 13 lines]
>
> www.OfficeArticles.com
Anne Troy - 23 Jul 2005 00:50 GMT
THAT's the answer I wanted. Thanks!!
*******************
~Anne Troy

www.OfficeArticles.com

> In a formfield calculation, you cannot use a formfield that contains the
> result of another calculation.
[quoted text clipped - 16 lines]
> >
> > www.OfficeArticles.com
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.