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

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
Asked:
1agordon
  • 4
  • 2
1 Solution
 
Bob ScriverCommented:
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
 
NevaarCommented:
Try making it simpler:

=IIF([% of Total]<0.5,"CoreA",IIF([% of Total]<0.7,"CoreB",IIF([% of Total]<0.85,"NonCoreA","NonCoreB")))
0
 
Bob ScriverCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Bob ScriverCommented:
Correction:

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

0
 
Bob ScriverCommented:
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
 
1agordonAuthor 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
 
1agordonAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now