MS Access IIF Statement Missing Closing Parenthesis

Question

I am struggling to figure out what's missing in my expression below. I appreciate all the help!

SKU_Qty: 
Sum(IIf( [SKU Breakout 004]![Year_Week] >= [z Calendar Reset]![Year-Week-Disco] AND [SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Ship], IIf( [SKU Breakout 003]![Distribution Type] = "DELETE",0,[SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty], IIf([SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Disco], [SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty] ,  [SKU Breakout 003]![SKU_Cust_Mix_Future%]*[SKU Breakout 004]![Qty]))

You might want give more detail – Mahendra Gunawardena Sep 19 '16 at 02:22 — Mahendra Gunawardena, Sep 19 '16 at 02:22

score 0 · Answer 1 · answered Sep 20 '16 at 20:10

You have 4 opening parentheses and only 2 closing, so you should close the other two as well.

You should outline your expression a bit, to not get lost in how it nests:

Sum(
    IIf( 
        [SKU Breakout 004]![Year_Week] >= [z Calendar Reset]![Year-Week-Disco] 
        AND [SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Ship], 
        IIf( 
            [SKU Breakout 003]![Distribution Type] = "DELETE",
            0,
            [SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty], 
            IIf(
                [SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Disco], 
                [SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty] ,  
                [SKU Breakout 003]![SKU_Cust_Mix_Future%]*[SKU Breakout 004]![Qty]
                )
           )

Now you see clearly that there is something is missing. Maybe you need this:

Sum(
    IIf( 
        [SKU Breakout 004]![Year_Week] >= [z Calendar Reset]![Year-Week-Disco] 
        AND [SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Ship], 
        IIf( 
            [SKU Breakout 003]![Distribution Type] = "DELETE",
            0,
            [SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty]
        ), 
        IIf(
            [SKU Breakout 004]![Year_Week] < [z Calendar Reset]![Year-Week-Disco], 
            [SKU Breakout 003]![SKU_Cust_Mix_Current%]*[SKU Breakout 004]![Qty] ,  
            [SKU Breakout 003]![SKU_Cust_Mix_Future%]*[SKU Breakout 004]![Qty]
            )
       )
)

MS Access IIF Statement Missing Closing Parenthesis

1 Answers1