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

Posted on 2010-01-06
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
    LVL 119

    Expert Comment

    by:Rey Obrero
    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


    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

    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?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now