Embedded IIF in Query - HELP 2

[elaineva]

I'm trying to use embedded IIF Statement in a query (first time using it) and I keep getting the error "expression you entered is missing a closing parenthesis,bracket or vertical bar". The first 2 IIF statements work great but when I add the last two - I get the error.

Can anyone tell me what's wrong??

RStatall: IIf([RStatus] Is Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0,2,IIf([EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]<5 And [SumOfRPAY]>=0 Or [EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc] Is Null And [SumOfRPAY]>=0,12,IIf([EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]>=5 And [SumOfRPAY]>=0,11, IIf([EecPayGroup]=&quot;RETIRE&quot; And [SumOfRPAY]>=0,14))

 

[28816]

You have 4 inbedded IIF statements and only 2 closing parens. You need 2 more.
The last IIF statement is missing a &quot;False&quot; statement.

 

[Aubs010]

missing two &quot&quot;'s at the end. Also, you could do with putting a final &quot;&quot; in at the end of the last IIF, because if it's false, I don't know wha tit would put (maybe an error)

so:

try this (I have coloured the parts you need to look at):

RStatall: IIf([RStatus] Is Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0,2,IIf([EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]<5 And [SumOfRPAY]>=0 Or [EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc] Is Null And [SumOfRPAY]>=0,12,IIf([EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]>=5 And [SumOfRPAY]>=0,11,IIf([EecPayGroup]=&quot;RETIRE&quot; And [SumOfRPAY]>=0,14,&quot;&quot;))))


As a tip, this is how I normally look at IIF or IF statements if I think (or know!!) there is a problem:

IIF(
   Condition,
   True,
   False
)

and nested like this:

IIF(
   Condition,
   True,
   IIF(
      Condition,
      True,
      False
   )
)

so, for the code you supplied, I would have looked at it in this format and highlighted what I saw missing:

RStatall:

IIf(
   [RStatus] Is Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination] Is Null And [SumOfRPAY]>=0 Or [RStatus] Is Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0 Or [RStatus] Is Not Null And [EecDateOfTermination]>[YREND] And [SumOfRPAY]>=0,
   2,
   IIf(
      [EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]<5 And [SumOfRPAY]>=0 Or [EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc] Is Null And [SumOfRPAY]>=0,
      12,
      IIf(
         [EecDateOfTermination] Between [YRBEG] And [YREND] And [RVesting Svc]>=5 And [SumOfRPAY]>=0,
         11,
         IIf(
            [EecPayGroup]=&quot;RETIRE&quot; And [SumOfRPAY]>=0,
            14,

&quot;&quot;

         )
      )

)
)



Aubs

 

[elaineva]

Thanks so much - both of you. Works like a charm!!

 

[Aubs010]

Pleasure Elaineva, thanks for the

. ;-)

Doh 28816, Can't believe you beat me to it - I got the post as soon as it was posted! - Must be because I'm slow!!! lol



Aubs