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

 

[Marshall Barton]

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.

One thing to remembar about aggregate functions (Count, Sum,
etc) is that they only operate on fields in the form's
record source table/query. You can use an expression with
fields, ordinary functions and numbers, but you can not use
controls.

I suspect that IsValid is not the name of a field in your
table.

 

[Andrew Backer]

Yes, both variables are fields in the form's query. I have tried again
with a new form where I was even more careful, and it's still turning
out the same.

The only thing that changed was that I moved to an ADP from an MDB, so
I changed the recordsource to the new table name (removed a space).
Other than that, field names returned, control names, eveyrything else
is ok.

I can sum( LineTotal ) just fine, but if I do any multiplication/etc
inside there it fails. As I showed, it fails if I set the source =
sum(3*5), so I don't think it's anything to do with what is coming out
of the db, but something specific to ADPs.

Does anyone have a clue? This makes working with forms and summaries
pretty difficult. Since this is a core part of access, especially
reports, I am very worried.

Thanks again,
Andrew Backer

 

[giorgio rancati]

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 )

 

[Andrew Backer]

I've taken a look at that (and one other one on a similar issue), and
really the resolution is thus : " It doesn't work , do something else "
It means that sum() doesn't work, since all I can do is sum a single
field. I know I can do whatever type of calculation I want in my
views, but these shouldn't be there, and are not there by design.

** sum(field) on a VIEW is also breaking for me. I can sum on the
table if I select from it directly, but I can't sum on the same field
if it comes from a view.

As a note again, why would sum(3*5) break? This says to me that
something is majorly messed, and it has nothing to do with what I am
doing.

I am coming to the conclusion that ADPs break a good portion of access,
as well as working great in others. This is extremely frustrating.

- Andrew

 

[Marshall Barton]

Yes, both variables are fields in the form's query. I have tried again
with a new form where I was even more careful, and it's still turning
out the same.

The only thing that changed was that I moved to an ADP from an MDB, so
I changed the recordsource to the new table name (removed a space).
Other than that, field names returned, control names, eveyrything else
is ok.

I can sum( LineTotal ) just fine, but if I do any multiplication/etc
inside there it fails. As I showed, it fails if I set the source =
sum(3*5), so I don't think it's anything to do with what is coming out
of the db, but something specific to ADPs.

Does anyone have a clue? This makes working with forms and summaries
pretty difficult. Since this is a core part of access, especially
reports, I am very worried.

I apologize. I missed the part about this being in an .adp
using SQL Server. Please forget that I tried to help.

 

[giorgio rancati]

** sum(field) on a VIEW is also breaking for me. I can sum on the
table if I select from it directly, but I can't sum on the same field
if it comes from a view.

Where is the difference ?

Select From Table
----
Select ......... ,
Sum( CASE WHEN [IsValid]=1 THEN [LineTotal] ELSE 0 END) AS SumField
From MyTable
Where ....
Group By .......
----

Select From View
----
Select ......... ,
Sum( CASE WHEN [IsValid]=1 THEN [LineTotal] ELSE 0 END) AS SumField
From MyView
Where ....
Group By .......

 

[giorgio rancati]

Select From View
----
Select ......... ,
Sum( CASE WHEN [IsValid]=1 THEN [LineTotal] ELSE 0 END) AS SumField
From MyView
Where ....
Group By .......
----

In the form footer set the control source of the textbox to this expression

 

[Andrew Backer]

Sorry bout the ADP issue too. I tried to stop the posting, and posted
another one with (ADP) in the subject, but they both went up.

As to views vs. tables, the difference is that sum(fieldname) is not
working if the 'fieldname' came from a view. I don't know why; I am
very surprised.

I can do cases inside my select (as opposed to just linking to the view
directly), but I don't feel I should have to. I can think of enough
other workarounds that I can do, but shouldn't have to.

Still trying to figure out why/if sum() and iif() are brokin in forms
in ADPs? I have *tons* of forms with summaries, reports with sums and
page sums, and more. Crafting custom sql for each is not something I
would like to, but I will if I must. I guess the least-worst is
sticking it in the view, and then hoping that it's not too big a
performance drag. One problem I run into is that IsValid can be a
calculated field as well, which causes real proble.s. *arg*

This is going to make it a lot messier than it should be. Oy!

Thanks for your help,
Andrew Backer

 

[Andrew Backer]

Giorgio,

Well, I have gone ahead and done what you suggested. I can't find a
more convenient way to do this while also allowing editing easily (ie.
no stored procs), and with the proper indexes the performance hit is
acceptable.

I have worked out procs and views that calculate all the summary data
that I need for this particular form, and I may use them if I can share
them across multiple forms, or figure out some easy/quick way to
communitcate 'update the summaries' to the parent form when someone
edits the subform. Just gonna keep the changes to a minimum for now.

It turns out that if you do not type the field ( via cast/convert) then
access can not sum it! That's all it was

Marsh, no offense meant. I'm obviously pissed at MS, not you

 

[Andrew Backer]

Ok... one more god damn followup. This is really pissin me off.

I can't use sum(field) in a form footer if the subform is linked and
the field comes from a view. If I set the serverfilter property of the
subform manually (and turn off linking), then sum(..) works. Still not
really sure the root of this, but now I can get the sums. Oy!

What the hell is going on here MS? grumble grumble.

- Andrew

 

[Andrew Backer]

One more wrinkle... I have to link AND manually set the subform's
filter property to get summing to work. I'm tracing the sql, so it's
not generating any extra calls, so I can't see why it matters.

But... I can't rely on the ServerFilter property of the subform, since
setting it doesn't appear to do anything (w/o a requery?), and it just
shows *all* the fields.

WTH is going on

 

[Marshall Barton]

Marsh, no offense meant. I'm obviously pissed at MS, not you

No problem.

 

[Andrew Backer]

Well, this is how I am doing it now. I have just wrapped the
'resetter' in a static so it only gets called the first time the form
is opened. I have all my opens already wrapped in a function, so this
was simple.

DoCmd.OpenForm "form", acDesign, , , , acHidden
Forms("form").ServerFilter = ""
DoCmd.Close acForm, "form", acSaveYes

Unfortunatly this doesn't work in an ADE/MDE, so I guess there will
have to be some other way to ensure that it is set, possibly a utility
function in my ADP that gets run before I export to ADE. Something
like that.

- Andrew