# Syntax for IF formula when testing for several conditions

• 0
• Question
• Updated 3 years ago

I am having issues in understanding IF formula syntax.  Coming from MS Access VB background,  I need to understand how AND or OR work in QB.  I have a simple situation where I am testing for a multiple conditions.

If(

[Var Category] = "Variation Claim By Builder",

If(

[Variation Status] = "Approved", "BVR Approved", "BVR Pending"))

But I am not getting the right answer.  What do you use for OR operator in formula?

• 3,152 Points

Posted 3 years ago

• 0
• 72,478 Points
Can you write in engish words the logic that you would like to have?  Then I can provide the syntax.
• 31,698 Points
If( [Field 1]="Condition 1" AND [Field 2]="Condition 2", "If both true Response" , "If either or both false Rseponse" )

If( [Field 1]="Condition 1" OR [Field 1]="Condition 2", "If either condition is true" , "If neither are true" )
• 72,478 Points
As this is not an english sentence, I still am not understanding what you are trying to say.

But to try to answer in a general sense, when an IF calculates, it just keeps evaluating the listed conditions until it either finds one that is true or it runs out of tests to try and a default "else" is provided. be sure to list your conditions in the order that they should be tested as the formula will take the first condition it finds as true.

For example
IF(
[The sky is polkadot]="yes" or [The ocean is red] = "yes", "we are not on earth",
[The sky is blue] = "yes" and [The ocean is dry] = "yes", We are not on earth",
[The sky is blue] = "yes" and [The ocean is wet] = "no", "We are not on earth",
[The sky is blue] = "yes" and [The ocean is wet] = "yes, "We are on earth", "we are not sure where we are")

The above would calculate to "we are on earth".

here is some help on IF.
https://www.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cqd

Feel free to post back if you are stuck.  Perhaps and example would help me understand your desired logic tests.
• 3,152 Points
Thanks Matt.  I two fields and I am testing both for various conditions which is as under:

If var category is equal to statement 1 and var status is equal to approved, then categorise the record as "BVR Approved".  If the variation status is still not approved, then call it "BVR Pending". Then I have a second set of condition using the same var category testing for statement 2 and whether or not var status is approved and these are called DVR Approved and DVR Pending.  I worked out the following formula which seems to be working but might run past and Mark.

If(
[Var Category] = "Variation Claim By Builder" and [Variation Status] = "Approved", "BVR Approved",
If(
[Var Category] = "Variation Claim By Builder" and [Variation Status] <> "Approved", "BVR Pending",
If(
[Var Category] = "Change to Issued Drawing" and  [Variation Status] = "Approved By PCCG", "DVR Approved",
If(
[Var Category] = "Change to Issued Drawing" and  [Variation Status] <> "Approved By PCCG", "DVR Pending", ""))))
• 72,478 Points
Arshad, sorry I got confused between your post and Matt chiming in.

Do not nest your IFs, that will just confuse you with the extra brackets.

IF(
[Var Category] = "Variation Claim By Builder" and [Variation Status] = "Approved", "BVR Approved",

[Var Category] = "Variation Claim By Builder" and [Variation Status] <> "Approved", "BVR Pending",

[Var Category] = "Change to Issued Drawing" and  [Variation Status] = "Approved By PCCG", "DVR Approved",

[Var Category] = "Change to Issued Drawing" and  [Variation Status] <> "Approved By PCCG", "DVR Pending")

Also you do not need to say that the alternative is null ie empty quotes as if your are silent about the else result,  it will be null anyways.
• 3,152 Points
Thanks a lot for your help.  I have amended as advised. Works fine.  Is there a good resource on IF formulae I can read?
• 72,478 Points
• 3,152 Points
Thanks a lot.