How to Sum a Calculated Field in a Form Footer   Access 2007

Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
I have a Form (Datasheet is the default view) that contains a caluated field.   (RevisedBill).  I want to total all the rows , I created a Text Box in the footer of the form using:

=sum(RevisedBill)  I get nothing in the box no, error or anything just a blank field.

Any help?
Question by:seamus99
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26191779
you will not see anything in the footer of the form in Datasheet view.
use Continuous Forms as the default view,
use this in the Control Source of the textbox
LVL 14

Expert Comment

ID: 26191805

You can only apply those aggregate (across-records) functions to fields in the form's field list.

It sounds like you've got a text box that does a calculation in its control source, like =Price - (Price * Nz(Discount)). Access will not let you Sum, Max, Min across those text box controls.

Instead, create a query with all of the underlying table or query's fields and an additional field, RevisedBill: Price - (Price * Nz(Discount)). RevisedBill will then appear on the form's field list. Change the Control Source of the textbox with the calculation to: RevisedBill (no equals sign). Then you can add a control with an aggregate function of this field, like =Sum(RevisedBill) or Max(RevisedBill).


LVL 14

Accepted Solution

pteranodon72 earned 2000 total points
ID: 26192079
I overstated that. You can only use aggregate functions on fields in the field list or calculations on those fields, not control names. So in my previous post 26191805, set the form footer text box's control source to:
=Sum([Price] - [Price]* Nz([Discount]) would work without creating the query.

But capricorn1's point remains - forms in datasheet view do not show form headers or form footers, so how are you seeing a blank box? Is the form being used as a subform? Are you seeing a space to enter new records, perhaps with default values in most fields but a blank under the calculated control?


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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