Solved

Multiple IIf conditions in report

Posted on 2002-05-01
7
1,523 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 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