Solved

Multiple IIf conditions in report

Posted on 2002-05-01
7
1,532 Views
Last Modified: 2012-06-21
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
Comment
Question by:1agordon
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6984348
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
 
LVL 4

Expert Comment

by:Nevaar
ID: 6984359
Try making it simpler:

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

Expert Comment

by:Bob Scriver
ID: 6984377
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6984380
Correction:

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

0
 
LVL 3

Accepted Solution

by:
Bob Scriver earned 50 total points
ID: 6984390
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 Comment

by:1agordon
ID: 6984401
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 Comment

by:1agordon
ID: 6984442
A little tweaking on the switch statement worked.  Hey, thanks!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question