?
Solved

Totaling a calculated field

Posted on 2011-10-24
6
Medium Priority
?
260 Views
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.

Sum(text43)
or
Sum(IIf([Awarded]=True,[Text43],0))

are both asking for the values of [Text43]

and

=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.

Thanks,
Michael
0
Comment
Question by:MCinOH
  • 3
  • 2
6 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 37019014
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.

mx
0
 
LVL 75
ID: 37019024
And then as far a formatting, you can just set the Format property of your Sum control on the report to Currency.

mx
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37019027
You need to use the value of the underlying field, not the control.

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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Expert Comment

by:mbizup
ID: 37019057
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.
0
 

Author Comment

by:MCinOH
ID: 37019081
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37019112

<<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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

862 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