Solved

Help understanding IFF(

Posted on 2011-09-12
5
409 Views
Last Modified: 2012-05-12
Experts,
I'm trying to understand a query I've inherited that encompasses IFF( as seen below:

IIf([check amount] Between 60 And 80,9,IIf([check amount] Between 80 And 100,12,IIf([check amount] Between 100 And 120,15,IIf([check amount] Between 120 And 140,18,IIf([check amount] Between 140 And 160,21,IIf([check amount] Between 160 And 180,27,IIf([check amount] Between 180 And 200,30,IIf([check amount]>200,30,0)))))))) AS [held fee]

Can somebody please explain in English what's going on here?
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
ID: 36524323
This is the basic logic with the input value ([check amount]) on the left and the output ([Held Fee]) on the right.

Check Amount     Held Fee
60-80                      9
80-100                    12
100-120                  15
120-140                  18
140-160                  21
160-180                  27
180-120                  30
>200                      30

Or is it the whole syntax of the Iif you want explained?
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 36524339
Nested IIF statements can be confusing.  This is the same as:


If [check amount] Between 60 And 80 Then
    Held Fee = 9
elseif [check amount] Between 80 And 100 then
    Held Fee = 12
Elseif [check amount] Between 100 And 120 then
    Held Fee = 15
Elseif [check amount] Between 120 And 140 then
    Held Fee = 18
Elseif [check amount] Between 140 And 160 then
    Held Fee = 21
Elseif [check amount] Between 160 And 180 Then
    Held Fee = 27
Elseif [check amount] Between 180 And 200 Then
    Held Fee = 30
Elseif [check amount]>200 then
    Held Fee = 30
Else
    Held Fee = 0
End If

Of course you cannot use BETWEEN like this in vba code, but you get the idea.  Personally, I prefer to use the Switch function.  In your query, it would look like:

SWITCH([check amount] < 60, 0, [check amount] < 80, 9, [check amount]< 100, 12, [check amount] < 120, 15, [check amount] < 140, 18, [check amount] < 160, 21, [check amount]<180, 27, [check amount] >= 180, 30, True, 0)

which is a little easier to read, and because the criteria are evaluated in order, you don't need to include the lower limit in the equation.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36524346
the first IIF
IIf([check amount] Between 60 And 80,9  
   * is testing the value of [Check AMount] is in the range of 60 to 80 and to return a value of 9

   * if the value is not satisfied by the first condition ( range of 60 to 80 ) it test the next
IIf([check amount] Between 80 And 100,12

and so on,

and if nothing in the IIF expression was satisfied , return a value of 0


*NOTE

you have an overlapping ranges of values

Between 60 And 80

Between 80 And 100


that should written like this

Between 60 And 79

Between 80 And 99

Between 100 And 119        

and so on


0
 

Author Closing Comment

by:Frank Freese
ID: 36524353
thanks - I noticed the overlapping values - these developers just did such a poor job
0
 
LVL 33

Expert Comment

by:Norie
ID: 36524478
By the way, you could probably cut the expression down.

I tried a few things but the 'blip' from 21 to 27 throws it off.
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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

733 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