I am running a crosstab query and have created a new field. This new field needs to evaluate the condition of several of the crosstab columns and then produce an answer.
I have ran out of space using a Switch command or IIf statement and was thinking I could create a Select Case situation but I have little VBA experience.
Here is my what I need. (Short version)
Fields in qry.
Plan, JobID, EmployeeID,TotalSales,TotalBonus,BonusAmt(I need to create this field using this select statement)
I need to create something similiar to this but do not know how to create the function and I am sure this code is wrong but hopefully you can get what I am trying to do.
Public Function CalcBonus() As Currency
Dim ? as Integer
? = ?
Select Case ?
Case 1
[Plan]=1 and [TotalSales]>=50000
NewField(BonusAmt) = $500.00
Case 2
[Plan] = 2 and [TotalSales]>=60000
NewField(BonusAmt) = $750.00
Case Else
Newfield(BonusAmt) = 0
End Case
There are at least 15 case possibilites and there are other fields that will be evaluated in the case statement but those I have working fine in the switch statements that I have. The only problem is its too long.
Once I have the code working, I suppose in the qry that I am running I will create the newfield and it will call this function? Maybe, BonusAmt:CalcBonus()
Please help. I need this done today if at all possible.