IIF Statement is too long. Need VBA option

[ahoneycutt]

I have a need to evaluate 185 records and perform a calculation for each record using IIF statements but my IIF's are too long.

Basically I want to create a new field called BonusAmt. In this filed I need it to evaluate different columns and produce an answer per row.

I do not know VBA very well but I know there has got to be solution for people who's nested iff's are too long.

Here is an example of my iif statements.

If [Plan] = 1 And [TotalBonus] >= [50%_Min_BonusOp%] Then
[BonusAmt] = [TotalBonus]

ElseIf [Plan] = 1 And [EBIT] >= [50%_Threshold] And [EBIT] <= [50%_Max] And [TotalBonus] <= [50%_Min_BonusOp%] And [EBIT] > [30%_Min_BonusOp%] Then
[BonusAmt] = [50%_Min_BonusOp%]

ElseIf [Plan] = 1 And [EBIT] >= [30%_Threshold] And [EBIT] <= [30%_Max] And [TotalBonus] >= [30%_Min_BonusOp%] Then
[BonusAmt] = [30%_Min_BonusOp%]

ElseIf [Plan] = 2 And [TotalBonus] >= [50%_Min_BonusOp%] Then
[BonusAmt] = [TotalBonus]

ElseIf [Plan] = 2 And [Direct Control Profit] >= [50%_Threshold] And [Direct Control Profit] <= [50%_Max] And [TotalBonus] <= [50%_Min_BonusOp%] And [Direct Control Profit] > [30%_Min_BonusOp%] Then
[BonusAmt] = [50%_Min_BonusOp%]

ElseIf [Plan] = 2 And [Direct Control Profit] >= [30%_Threshold] And [Direct Control Profit] <= [30%_Max] And [TotalBonus] >= [30%_Min_BonusOp%] Then
[BonusAmt] = [30%_Min_BonusOp%]
ElseIf [Plan] = 3 And [TotalBonus] >= [50%_Min_BonusOp%] Then
[BonusAmt] = [TotalBonus]

ElseIf [Plan] = 3 And [Gross Margin $] >= [50%_Threshold] And [Gross Margin $] <= [50%_Max] And [TotalBonus] <= [50%_Min_BonusOp%] And [Gross Margin $] > [30%_Min_BonusOp%] Then
[BonusAmt] = [50%_Min_BonusOp%]

Else [BonusAmt] = "0"

Can someone give me an example of what I would do considering I do not know how to write VBA?

Thanks in advance. I need to figure this out ASAP.

 

[jdraw]

You may get some ideas from this.
vba is a bigger topic than this 1 example.

FunctionX is a good source of info and there are several good youtube videos. But experience by doing and learning is a journey.

Getting your tables and relationships designed to meet your business needs is critical.
Good luck.

 

[CJ_London]

you could try breaking your calcs into separate columns - perhaps one for each plan - each calc to return 0 if no conditions are met. Then have a final column to total all plans

 

[namliam]

Easiest way to push this to a function is to go into vba and make one...

public function YOURNAME(Field1 as date, Field2 as double, field3 as string, etc...) as double
' do if's etc needed
If this then 
   YOURNAME = Result
elseif that then
   YOURNAME = result2
Else
   YOURNAME = 0
Endif

End Function