Duane,
I apologize for not getting back sooner. I appreciate your suggestions
although I don't understand this most recent one. Where is the record
source
view? I'm looking at properties and see the record source property. I'm
reluctant to do this as it's not just 196 that I'm trying to sum in my
report
footer. I'm also trying to sum 196A, 196B, etc up to 196J so I suspect
this
might not be a good option for me. For now I'm sticking with the Access
.mdb
format which does seem to work fine with the IIF statement I orginally
pasted
here.
thanks again,
George
Duane Hookom said:
What do you get if you try add this to your report's record source view:
Case When [Section_List]='196' Then [Related_Costs] Else 0 End As
Only196Costs
You should be able to sum the column Only196Costs in your report.
--
Duane Hookom
MS Access MVP
--
gmead7 said:
Thanks, I tried it and received the same message. I guess this is one
of
those situations where "should work" and "will work" . For whatever
reason
the .adp Project file does not like the nested expressions. It does
the
Sum
in the group by section because it's not nested; simply
SUM[Related_costs].
I did find a work around by creating a function and then adding a
subreport
to the report... it works, but I doubt if it's the best way to do it...
:
Whatever works in a group footer should work in a report footer. Most
likely
the expression won't work in a Page Footer.
Abs(YourExpression) will evaluate to either True/-1 or False/0.
Multiply
the
Absolute value of the expression times the field that you would like
to
sum/total and you get a sum of the records where the expression is
true.
--
Duane Hookom
MS Access MVP
Duane, Can you help me understand what this argument does? It
looks
like
it
takes the absolute value of a text field and multiplies it by
'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up
all
the
Related costs where the Section list (code) is Section 196. I want
to
do
this in the Report Footer (I can do it in the groupings using a Sum
statement)
thanks, George
:
Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.
--
Duane Hookom
MS Access MVP
--
Thanks - it was worth a try, but it returned the same error
message.
:
Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field
At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top
:
I had a report in Access .mdb that provides summary data
through
an
expression as per below:
=Sum(IIf([Section_List]="196",[Related_Costs],0))
We have pushed the tables out to SQL Server and converted to
Access
Project
.adp
Now this expression won't work and yields this error when you
try
to
run the
report:
"The Expression _____ is Invalid. Aggregate functions are
only
allowed
in
ouput fields of the Record Source."
I'm seeking any help to fix this in adp / Access Project.
thanks,
GM
