Combine IF Statement

[nike2000]

Hi, I am looking to combine the following IF Statements, I need some help...

The statements are:

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes","No&quot

=IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes","No&quot

Thanks,
Nike

 

[Trendsetter]

How about:

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes","No&quot OR IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes","No&quot

seems OK to me!

 

[scriverb]

The Switch statement offers the ability to have multiple expressions evaluated with a returned result for a positive(true) answer. The order in which they are entered is important. If the expression 1 is found to be true then that is as far as the expression searches for an answer. It would only move on to expression 2 if expression 1 was false. The number of expressions is almost unlimited.

ACCESS Help:
Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.
Syntax
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

Switch(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes", Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes", True, "Yes&quot



Bob Scriver

 

[scriverb]

Sorry the final expression True should return a "No":

Switch(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes", Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes", True, "No&quot

Bob Scriver

 

[nike2000]

Thanks for getting back to me.
The Switch function has worked but I need it to display 'No' if the above criteira isn't applied.
Would I still need to use the Select Statement?
Thanks again,
Nike

 

[Aubs010]

Look at these two (your original IF statements)

(a) =IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes","No&quot

(b) =IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes","No&quot


Then, please provide the answers to these quesitons...

if (a) returns "Yes" and (b) returns "No" what would you want it to display?
if (a) returns "Yes" and (b) returns "Yes" what would you want it to display?
if (a) returns "No" and (b) returns "No" what would you want it to display?
if (a) returns "NO" and (b) returns "Yes" what would you want it to display?

Will try and help then if I can.

Aubs

 

[nike2000]

Hi Aubs,
Thanks for your help.
Below are the answers to your questions.
Thanks again.
Nike



if (a) returns "Yes" and (b) returns "No" what would you want it to display?

Yes

if (a) returns "Yes" and (b) returns "Yes" what would you want it to display?

Yes

if (a) returns "No" and (b) returns "No" what would you want it to display?

No

if (a) returns "NO" and (b) returns "Yes" what would you want it to display?

Yes

 

[Aubs010]

see if this does the trick:

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes",IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes","No&quot)

Aubs

 

[nike2000]

Thanks it worked a treat.
I have a third Control Box to add with an additional If statement to add to the above.

The If statement is:

=IIf(Abs(([FOCUS Delta]-[MAD Delta])/([FOCUS Delta])*100)>1,"Yes”, "No&quot

Can this be added too.
The same logic applies.
Thanks again.
Nike

 

[Aubs010]

Here's your answer...

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes",IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes",IIf(Abs(([FOCUS Delta]-[MAD Delta])/([FOCUS Delta])*100)>1,"Yes”, "No&quot))


This is how I look at code if I'm trying to figure out where there is a problem or if I don't know where to put something...
(copy and paste it into Notepad to view it properly - Make sure word wrapping is off!)

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,
    "Yes",
    IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,
       "Yes",
       IIf(Abs(([FOCUS Delta]-[MAD Delta])/([FOCUS Delta])*100)>1,
          "Yes”,
          "No"
       )
    )
 )

Walking through it...

                    =IIf(<condition>,
is true, then:          &quot;Yes&quot;,
is Not true, then:      IIf(<condition>,
is true, then:             &quot;Yes&quot;,
is Not true, then:         IIf(<condition>,
is true, then:                &quot;Yes&quot;,
is Not true, then:            &quot;No&quot;
End Third IIF              )
End Second IIF          )
End First IIF        )

Aubs

 

[nike2000]

Aubs,
An error message has appeared. The message is:

'The expression you entered contains invalid syntax'

'You may have entered an operand wityhout an operator'

Thanks,
nike

 

[Aubs010]

the " in the last "Yes" was different!!

=IIf(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes",IIf(Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes",IIf(Abs(([FOCUS Delta]-[MAD Delta])/[FOCUS Delta]*100)>1,"Yes","No&quot))

Aubs

 

[scriverb]

In answer to your post concerning the Switch statement a "No" will be applied if the first two expressions are evaluted as false. That is why there is a third expression True which when evaluated will return True so the "No" will be returned.

As for the question about the select you hadn't mentioned anything about a query yet. You just asked about how to make the IIF statement work. Yes, a query could be used with these statments where you are creating a new column:
Select *, Switch(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9,"Yes", Abs([FOCUS PV])-Abs([MAD PV])>30000,"Yes", True, "No&quot as NewDataField
FROM tblYourTable;

Let me know if this doesn't work as advertised.

Bob Scriver

 

[Aubs010]

Further to my previous post, an even easier way would be:

=IIF(<condition1> Or <condition2> Or <condition3>,&quot;Yes&quot;,&quot;No&quot

Giving:


=IIF(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9 Or Abs([FOCUS PV])-Abs([MAD PV])>30000 Or Abs(([FOCUS Delta]-[MAD Delta])/[FOCUS Delta]*100)>1,&quot;Yes&quot;,&quot;No&quot

Hope it works!

Aubs

 

[nike2000]

Thank you all for your help.
I have a further problem in this Statement.
Some values are negative and I have tried to avoid this by entering the Absolute Value.
This problem takas place when it identifies those records over 30,000, anything over -30,000 also needs to be flagged as yes.
Would I then need to to include -30,000 to identify these records.
Thanks again.
Nike

 

[Aubs010]

Would I then need to to include -30,000 to identify these records.

Try it and see

to answer it though, yes, you will!

=IIF(Abs(([FOCUS PV]-[MAD PV])/([FOCUS PV])*100)>9 Or Abs([FOCUS PV])-Abs([MAD PV])>30000 Or Abs(([FOCUS Delta]-[MAD Delta])/[FOCUS Delta]*100)>1 Or Abs([FOCUS PV])-Abs([MAD PV])>-30000,&quot;Yes&quot;,&quot;No&quot

Aubs

 

[nike2000]

Thanks guys worked perfectly thanks for great advice.
Nike

 

[Aubs010]

Pleasure

Aubs