If Then Statement

[KSCOTT]

I can't seem to get this formula to work. All I need to do is see if the Status column of my table = 70 and the J&R column is not H or J, I need to return the total IOH.

It looks right, however, it's not assigning total IOH if the J&R column is blank.

Formula:

NET RISK2: IIf([ZFREE]![STATUS]=70,IIf([ZFREE]![J&R] Not In ("H","J"),[ZFREE]![TOTAL INV COST]))

I appreciate any help I can get!

 

[Parra]

Hi Kscott, you could try to use and if & and function, something like:

NET RISK2: IIf(and([ZFREE]![STATUS]=70,([ZFREE]![J&R] Not In ("H","J")),IOH,[ZFREE]![TOTAL INV COST]))

If this does not work can you please list the name of the columns individualy. Also what do you want to happen if the criteria of your function is false? Thanks

 

[KSCOTT]

Hi...thank you for replying!

I typed in your formula and it says that the expression contains an invlaid syntax.

Columns:
Status
J&R
Total IOH
Net Risk2 = formula

Net Risk 2= If Status is 70 and J&R is not H or J, then give me the Total IOH, if not leave blank.

Thanks again!

 

[Parra]

Lets see if this helps.

Net Risk 2: IIf(and([STATUS] = 70, [J&R]<>”h” or “j”),[TOTAL IOH],0)

Let me know.

 

[KSCOTT]

It still says that I have invalid function. It looks like it doesn't agree with the AND.

 

[jobb]

see if this one works..

Net Risk 2: IIf(([STATUS]=70 and [J&R]<>”h” and [J&R]<> “j”),[TOTAL IOH],0)