(ADP) Summary fields in form footer not calculating (sum/iif)

[Andrew Backer]

Hello,

I am having a problem with summary fields in my subforms. I have quite
a few of these, and I use them to do rollups, totals, and such where it
take to long to calculte them in the database.

Basically... In the form footer I have textboxes with code like this :

=Sum( [IsValid] * [LineTotal] )
=Sum( iif([IsValid]=1, [LineTotal], 0 ) )
=Sum( 3*5 )

It's this type of code, or variations on it. I put the 3*5 in there
just to show that it would go boom on that. However, if I remove the
'sum', it works ok. These work :

= IsValid*LineTotal
= iif(....)
= sum(LineTotal) '-- notice, no complex expression.

Any idea why this doesn't work, and a possible way around it? I don't
want to do a physical recalculation each time I add a lineitem or other
calculation.

Thanks,
Andrew Backer

 

[Razvan Socol]

Hi, Andrew

Put the multiplications and the iif-s (written as "case when ... then
.... else ... end") in a query and the "=sum(the_result)" in the
textboxes.

Razvan

 

[Andrew Backer]

Thanks. I can put anything I need in a view, stored proc, etc but I
do not want to do that. I am doing this for performance reasons, so I
need to keep it this way. Also, there are too many cases where I need
this behavior and it breaks on all of them.

Any ideas why sum(3*5) breaks? Also, I can't sum on a view, at least
not on this particular form, so that won't work. I can sum on a table
though. Oh, and I already bastardized my table partially by adding one
calculated field just for this purpose

Ay, why do ADPs break in so many ways? Trying to port an app and
finding all sortsa wierd stuff. This is pretty major tho.

- Andrew

 

[Razvan Socol]

I can put anything I need in a view, stored proc, etc but I do

not want to do that. [...] Oh, and I already bastardized my table
partially by adding one calculated field just for this purpose

You don't need to save this query in a view. Just set the RecordSource
of the form to:

SELECT *,
CASE WHEN IsValid<>0 THEN LineTotal ELSE 0 END as ValidLineTotal
FROM YourTable

and set the ControlSource of the textbox to "=Sum([ValidLineTotal])".

Any ideas why sum(3*5) breaks?

It's documented in the KB Article 225992 (quoted by giorgio rancati, in
the other thread):
"Aggregate functions are only allowed on output fields of the Record
Source"

Razvan

 

[Andrew Backer]

Yeah, i've gone and put it in the view. Oh well, I guess this part
is broken in ADPs. It's a pretty big thing to be broken, thats all.

What I was saying was that, after doing that, I couldn't do the sum
anyway since it was coming from a view. However, I could do it from a
table. In the other thread of similar name I mentioned the solution
tho.. kinda funny.

I have a linked subform. In this config the sum() won't work, despite
the fact that everything shows like it should. To get it to work I
have to set the ServerFilter on the subform, or completely remove the
linkage and just set the sql on load manually.

Funky, but it's working now.

Sry for the confusion,
- Andrew Backer

 

[aaron.kempf]

Microsofts not responsible and they dont take Access seriously.

the problem is that MS is run by Excel dorks.

I think that the only answer is the breakup of Microsoft on anti-trust
grounds.
It has finally come to this.

-Aaron