conditional expression in report

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

Guest

is it possible to nest IIf statements?
should this code work?

=IIF([sales])<=500,([sales])*.0375,IIF([sales])>500 and
([sales]),=1000,([sales])*.055,iif([sales])>1500,([sales])*.075,([sales])*.10)
 
is it possible to nest IIf statements?
should this code work?

=IIF([sales])<=500,([sales])*.0375,IIF([sales])>500 and
([sales]),=1000,([sales])*.055,iif([sales])>1500,([sales])*.075,([sales])*.10)
Click to expand...

You have incorrect parenthesis placement as well as at least one logic
inconsistency.

What is the purpose of the comma after([Sales]) in
"and ([Sales]),=1000"?

Are you trying to say IIf([Sales]>500 and [Sales] = 1000,....
Isn't it enough to simply say IIf([Sales] = 1000?
Do you really mean IIf([Sales]>500 and [Sales]<=1000,...?

What is the purpose of surrounding each instance of [Sales] with it's
own set of parenthesis.

I would suggest you re-think what you want to accomplish and re-post,
this time using words so someone who doesn't know what you want can
understand.
 
There were too many parenthesis and an extra comma (in front of "=1000").

Try this:

(should the "=1000" be "<=1000"?)

=IIf([sales]<=500,[sales]*0.0375,IIf([sales]>500 And
[sales]=1000,[sales]*0.055,IIf([sales]>1500,[sales]*0.075,[sales]*0.1)))


Note that the second IIF() will multiply [sales]* .055 ONLY when [sales] =
1000

...... IIF([sales]>500 and [sales]=1000,.......

If [sales] is >500 and <=1500 (but not 1000), the result will be [sales]*.10.


HTH
 
Jim,
Not the way it's written...
Here's what you logic is saying, as best as I can figure...
If Sales <= 500 then * .0375
If Sales > 500 and = 1000 then * .55 (makes no sense...)
If Sales > 1500 then * .75 (what about 1001 to 1500?)
Else Sales * 1.0 (the fallout calc)

Before anyone can give you the correct syntax for an
nested IFF statement, you'll have to lay out your logic more clearly.
We need to see logical "bracket" ranges to apply the calcs to.
 
Back
Top