• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1577

# Multiple IIf conditions in report

Is it true that you can't tier IIf conditions in Access?  This has to be in a report, because it is based on a RunningSum calculation.

Expression I'm trying is below - should be pretty apparent on what I'm trying to do.  Any suggestions?  Anything that is not .5> is showing up as "CoreB".  The other two cases (NonCoreA and NonCoreB)aren't registering.

=IIf(0.5>[% of Total],"CoreA",IIf(0.5<[% of Total]<0.7,"CoreB",IIf(0.7<[% of Total]<0.85,"NonCoreA","NonCoreB")))
0
1agordon
• 4
• 2
1 Solution

Commented:
Try using the Switch statement.  This is a tiered function by your definition.  Switch( exp-1, fet-value1, exp-2, ret-val2, exp-3, ret-val3, . . . exp-n, ret-valn).  It starts with the first expression and searches from 1 thru n expressions until it finds the first one that is true and returns the corresponding return value for that expression.  If it finds nothing it returns a Null value.

This should work for your situation.

Bob Scriver
0

Commented:
Try making it simpler:

=IIF([% of Total]<0.5,"CoreA",IIF([% of Total]<0.7,"CoreB",IIF([% of Total]<0.85,"NonCoreA","NonCoreB")))
0

Commented:
This is how I would setup the Switch function statement:

=Switch([% of Total]<0.85,"NonCoreA",[% of Total]<0.7,"CoreB",[% of Total]<0.5,"NonCoreA", [% of Total]>=0.5,"NonCoreA")

Bob Scriver
0

Commented:
Correction:

=Switch([% of Total]<0.85,"NonCoreA",[% of Total]<0.7,"CoreB",[% of Total]<0.5,"NonCoreA", [% of Total]>=0.5,"NonCoreB")

0

Commented:
So that we can get this right could you spell out in words what you want returned with the different values of [% of Total].

Example:  .05 thru .7   CoreA
.07 thru .85   whatever.

The switch statement will do this really well but from your posting I am not sure exactly what your range of values and return values are.

Bob Scriver
0

Author Commented:
Clarification

0 thru .5    Core A
.5 thru .7   Core B
.7 thru .85  Non Core A
.85 thru 1   Non Core B

Trying switch statment gave me same problem as IIf - only two of 4 possible outputs show.
0

Author Commented:
A little tweaking on the switch statement worked.  Hey, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.