Totaling a calculated field

Posted on 2011-10-24
Last Modified: 2012-05-12
I have a report which I have added the following calculated field in the detail section.  

IIf([Awarded]=True,[Est Revenue],0)  (This field is [Text43])

This field calculates correctly.

I am now in the report footer trying to sum this field.


are both asking for the values of [Text43]


=Sum(IIf([Awarded]=True,[Est Revenue],0))
Doesn't test for true, it brings back a sum of all records.

Thoughts on how to format this sum?

Also, an additonal 100 points if someone can tell me how to format my calculated field [Text43] to Currency.  Setting the format to Currentcy in the control property doesn't work.

Question by:MCinOH
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Using Sum(), you can only apply that to actual Fields in your underlying table or query. So, one option is to use a calculated field in your query and refer to that.

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    And then as far a formatting, you can just set the Format property of your Sum control on the report to Currency.

    LVL 61

    Accepted Solution

    You need to use the value of the underlying field, not the control.

    Sum(IIf([Awarded]=True,[Est Revenue],0))

    LVL 61

    Expert Comment

    The reason I posted is essentially what mx stated in the first comment; the expression is an alternative that can be implemented right on your report versus in a query.

    Author Comment

    I did identify my formatting issue.  Apparently, if the text field is set as "Rich Text", it will not format based on the property.  Setting this back to "Plain Text" corrects this situation.

    Thanks everyone.
    LVL 61

    Expert Comment


    <<Setting the format to Currentcy in the control property doesn't work.>>
    << Apparently, if the text field is set as "Rich Text", it will not format based on the property.  Setting this back to "Plain Text" corrects this situation. >>

    Good to know!  That's something I wouldn't have guessed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Access DLookup 18 22
    MS Access question 11 39
    Aggregate Function error 7 25
    MS Access 2010 - Error 53-File Not Found 27 23
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now