Sum display Problem

[chevyimpala]

I would like to know if this is possible. I have a sum box in my account footer that has the following code:

=iif(sum([size])<>0,0,Sum([net]))

now can the following statment work in a sum box in my currency footer:

iif(sum([net])<>0,Sum([net]))

What I want this box to do is only add together the numbers that are showing in the report sums in the account footer so if it shows a 0 then is should not add it. Can anyone help!!

Thx.

 

[THWatson]

chevyimpala
If I understand your post correctly, what you want to do is...if Sum([Net]) is not equal to 0 you want to show it, otherwise if it's 0 not show it.

If that's the case, then all you are missing is a 0 in your IIf construct. In other words...
=IIf(Sum([net])<>0,Sum([net]),0)

So if Sum([net]) is not equal to 0, the True part is Sum([Net]), the False part is 0.

Does that do it?

Tom

 

[chevyimpala]

Well Tom,

Thats what I want it to do in the account footer. However in the currency footer I need it to add up the amounts in the account sum that dont have a zero displayed. Thats where the problem inlies becasue if I run that it adds up all the numbers even if it shows me a 0 on the report.

ex.
size net
sub total 60000 0

sub total 0 12000

sub total 25000 0
_________________________________________
grand total 85000 12000
-----------------------------------------

Even though there are numbers stored in the net field it should only add and display those that are not 0. Is that to complicated to do!

THX

 

[THWatson]

chevyimpala
It's a blizzard outside today, so pardon me if I'm having trouble seeing clearly!

However, I gather that what you are showing as &quot;sub totals&quot; are actually the Sum([net]) amounts, and you want to Sum those sub totals that are not 0. Correct?

So then, would this work...
=IIf(Sum([net])<>0, Sum(Sum([net])),0)

Or maybe it's...
=Sum(IIf(Sum([net])<>0,Sum([net]),0)

Tom

 

[chevyimpala]

thx

but now i get an error saying that I cant have an agregate funtion inside a expression. it doesn't like the Sum(sum([net]) inside the equation!
do you know any other alternatives.

 

[THWatson]

chevyimpala
I'm tempted to wonder why adding 0's is an issue. Doesn't 12000 + 0 + 0 still equal 12000?

Or maybe you can use the Nz function.
= Sum(Nz([net]))

I'm going to think about this a little, but offer those thoughts for the moment.

Tom

 

[THWatson]

chevyimpala
I'm having difficulty visualizing the layout of your report. You have a [size] field that is being totalled, I assume in its Group footer. You also have a [net] field that is being totally, I assume in its Group footer. Where does the Currency footer read its data from...or is it the report footer?

If the Currency footer is the report footer, then you should be able to sum the [net] fields from the report itself, irrespective of the Group footer.
=Sum([net])
But, again, this will total 0 + 12000 + 0, which still gives you a 12000 total, irrespective of how many 0's there are.

Sorry for not understanding. I'm sure it's clear at your end, just not at mine. Can you tell me a little bit more about the report? Any subreports? And where the Currency footer comes from?

Alternatively, you could send me a small sample of your db, extracting any sensitive information, and I could have a look at what you want to do. My e-mail address is twatson*at*sentex.net (change the *at* to the appropriate symbol)

Tom

 

[chevyimpala]

thxs Tom,

After much revamping of your earlier reply I was able to figure it out, now i just have to go in and make sume changes to a few entrys so that my number will add up. Thxs again.

Ivan

 

[THWatson]

Ivan
Great. The important thing is that you have it working properly now. Good going.

Tom