Results 1 to 6 of 6
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    multiple nested iif statements

    Hi all. I need the best way to apply multiple nested IIF statements in a field on a form. The following is the IIF statements:
    Code:
    IIF([sfrmCust].[Form]![DED]>0,IIF([Patcnt]*16<=1000,16*[Patcnt],1000),0)+
    IIF([sfrmCust].[Form]![SWE]>0,IIF([Patcnt]*24<=1000, [Patcnt]*24,1000),0)+
    IIF([sfrmCust].[Form]![PLI]>0,IIF([TPtcnt]*24*12<=10000, [TPtcnt]*24*12,10000),0)+
    IIF([sfrmCust].[Form]![Loc]*(IIF([Fre]=”Wk”,52,IIF([Fre]=”BWK”,26,IIF([Fre]=”SM”,24,IIF([Fre]=”M”,12)*10+
    RtR*IIF([Fre]=”Wk”,52,IIF([Fre]=”BWK”,26,IIF([Fre]=”SM”,24,IIF([Fre]=”M”,12)*1



    Do you think it is too much to put in the control source in the field on the form? Or should I put this in a module. Not sure if it's correct. Seeking guidance please.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would put it in a function. My eyes glaze over trying to follow formulas like that. Depending on what they are, I might also get values from a table rather than hard coding them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The expression isn't correct -

    In the last two lines there are way too few ")" for all those IIf's.

    Also, the two IIf's that look like this : IIF([Fre]=”M”,12) are wrong as well - an IIf requires three arguments, and you have only supplied 2 (the "False" part is missing).

    On the beginning of the last line - what is RtR ?

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe something like this aircode is easier to grasp.

    Code:
    dim resDED as long
    dim resSWE as long
    dim resPLI as long
    dim resLoc as long
    dim resPeriod as long
    dim resRTR as long
    dim resFinal as long
    
    
    select case [sfmCust].[Form]![DED]
        case > 0
            select case [Patcnt] * 16
                case <= 1000
                    resDED = [Patcnt] * 16
                case else 
                    resDed = 1000
            end select
         case else 
             resDed = 0
    end select
    
    
    select case [sfmCust].[Form]![SWE]
        case >0
            select case [Patcnt] * 24
                case <= 1000
                    resSWE = [Patcnt] * 24
                 case else 
                    resSWE = 1000
            end select
         case else 
            resSWE = 0
    end select
    
    
    select case [sfmCust].[Form]![PLI]
        case >0
            select case [TPtcnt] * 24 * 12
                case <= 10000
                    resPLI = [TPtcnt] * 24 * 12
                 Case else 
                    resPLI = 10000
            end select
         case else 
            resPLI = 0
    end select
    
    
    select case [sfmCust].[Form]![Loc]
        case >0
            select case [FRE]
                case 52
                    resFre = [Patent] * 16
                 case else 
                    resDed = 1000
            end select
         case else 
            resDed = 0
    end select
    
    
    select case FRE
        case "WK"
            resPeriod = 52
        case "BWK"
            resPeriod = 26
        case "SM" 
            resPeriod = 24
        case "M"
            resPeriod = 12
    end select
    resRTR = resPeriod * RTR
    resPeriod = ResPeriod * [sfrmCust].[Form]![Loc] * 10
    resFinal = resDED + resSWE + resPli + resLoc + resPeriod + resRTR
    Last edited by davegri; 09-02-2016 at 04:24 PM. Reason: fine tune

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Paul -- a table may be more appropriate, there's a lot of opportunity for syntax and logic errors in what you are showing.
    Again, it would be helpful to readers if you put more context around the issue when posting.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    In addition to 'eyeball glazing' and the opportunity for 'syntax and logic errors' (both statements which I wholeheartedly agree with!) in using this kind of stacking of IIFs...there's also a quirk, if you will, in using IIFs, even if only using a single iteration of it! Given

    FieldA = 2

    FieldB = 4

    FieldC = 0


    and

    Ans = IIf(Me.FieldA > 1, Me.FieldA + Me.FieldB, Me.FieldB / Me.FieldC)

    you would expect, with FieldA being > 1, that

    Ans = FieldA + FieldB

    or

    Ans = 6

    but what you'd get would be

    Error 11...Division by Zero!

    because even if the expression is True (as in this example...where Me.FieldA > 1...which is True) Access is still going to run the False part of the function, i.e. Me.FieldB / Me.FieldC...and Me.FieldC = 0!

    I will only use IIF() if the task has to be done using a single line (such as in a Calculated Field in a Query) and I can not think of any other way around it!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Nested If Statements?
    By shoelesscraig in forum Reports
    Replies: 7
    Last Post: 12-02-2015, 01:44 PM
  2. Getting mixed up with nested If statements
    By tonygg in forum Access
    Replies: 3
    Last Post: 03-28-2015, 08:53 PM
  3. Nested If statements and data validation...
    By d2ther in forum Database Design
    Replies: 11
    Last Post: 09-13-2013, 10:00 PM
  4. Nested If Statements Using Or (Logic Error)
    By IFA Stamford in forum Access
    Replies: 7
    Last Post: 12-30-2010, 08:53 AM
  5. Nested IIF statements?
    By laavista in forum Access
    Replies: 9
    Last Post: 06-22-2010, 10:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums