adding iif statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My individual iif statements work beautifully but I need to add them to get a
grand total. Is there a way to add the two iif statements in the control
source for a report? I am missing something and I don't know what it is.
Help!

=IIf([prod cat]="PE",[SumOfCommit],0)

=IIf([Prod Cat]="PE",
Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE",
Sum([SumOfAUM])))

I tried this and it didn't work:
=sum(IIf([prod cat]="PE",[SumOfCommit],0),
IIf([prod cat]="PE",
Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE",
Sum([SumOfAUM]))))
 
Looking at just the first line, you have
=Sum([expression] , ...
This certainly can't be what you want.
 
Luscious,

Well, I am not 100% clear what you are trying for here. For a start, the
bit about Sum([SumOfAUM])-[SumOfAUM] doesn't seem right somehow. But
anyway, based on what you have told us so far, I'll give it a go...

=Sum([SumOfAUM])+IIf([prod cat]="PE",[SumOfCommit]-[SumOfAUM],0)
 
Hi Steve,

Let's see if I can explain it better to you. The Prodcat is a category
checking for the category "PE" in order to pull a specific AUM number then
its subtracting it from the total SUM(AUM). But if there is no "PE" then it
adds the AUM's.

=IIf([Prod Cat]="PE", Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE", Sum([SumOfAUM])))

But in the meanwhile I will try your statement and let you know how it
turned out. Thank you for all your help and effort. :)

Sarita



Steve Schapel said:
Luscious,

Well, I am not 100% clear what you are trying for here. For a start, the
bit about Sum([SumOfAUM])-[SumOfAUM] doesn't seem right somehow. But
anyway, based on what you have told us so far, I'll give it a go...

=Sum([SumOfAUM])+IIf([prod cat]="PE",[SumOfCommit]-[SumOfAUM],0)

--
Steve Schapel, Microsoft Access MVP

My individual iif statements work beautifully but I need to add them to get a
grand total. Is there a way to add the two iif statements in the control
source for a report? I am missing something and I don't know what it is.
Help!

=IIf([prod cat]="PE",[SumOfCommit],0)

=IIf([Prod Cat]="PE",
Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE",
Sum([SumOfAUM])))

I tried this and it didn't work:
=sum(IIf([prod cat]="PE",[SumOfCommit],0),
IIf([prod cat]="PE",
Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE",
Sum([SumOfAUM]))))
Click to expand...
Click to expand...
 
Sarita,

Ok, that's what I assumed, and so the expression I suggested should be
pretty close to the mark. Let us know.
 
Hi Steve,

You are my angel...Thank you so much for all of your help. Here is the end
result:

=sum(IIf([Prod Cat]="PE",[SumOfCommit],0))+
IIf([Prod Cat]="PE",
Sum([SumOfAUM])-[SumOfAUM],
IIf([Prod Cat]<>"PE",
Sum([SumOfAUM])))

sarita


Steve Schapel said:
Sarita,

Ok, that's what I assumed, and so the expression I suggested should be
pretty close to the mark. Let us know.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Let's see if I can explain it better to you. The Prodcat is a category
checking for the category "PE" in order to pull a specific AUM number then
its subtracting it from the total SUM(AUM). But if there is no "PE" then it
adds the AUM's.

=IIf([Prod Cat]="PE", Sum([SumOfAUM])-[SumOfAUM],
IIf([prod cat]<>"PE", Sum([SumOfAUM])))

But in the meanwhile I will try your statement and let you know how it
turned out. Thank you for all your help and effort. :)

Sarita
Click to expand...
Click to expand...
 
Sarita,

Did you try it how I suggested earlier?...
=Sum([SumOfAUM])+IIf([prod cat]="PE",[SumOfCommit]-[SumOfAUM],0)

I feel sure this would give you the correct result. Whereas your
expression will too, it is not efficient, and also does not follow
standard syntax. You are testing for whether [Prod Cat] is "PE" three
times, where you really only need to test its value once. Also, an
IIf() function normally has 3 arguments: Condition, Truepart, Falsepart.
In the section of your expression:
IIf([Prod Cat]="PE",Sum([SumOfAUM])-[SumOfAUM],IIf([Prod
Cat]<>"PE",Sum([SumOfAUM])))
.... you have put IIf([Prod Cat]<>"PE" into the Falsepart argument of the
first IIf, whereas this is unnecessary because it is automatically true,
i.e. if it's not "PE" then it's not "PE", you already know. So it could
be just like this...
IIf([Prod Cat]="PE",Sum([SumOfAUM])-[SumOfAUM],Sum([SumOfAUM])))
Hope you understand.

Anyway, I think you didn't try the expression I gave you. If you did,
please let me know what was wrong with it :-)
 
Back
Top