Calculated Field Expression

[rbrule]

Help with expression in a calculated field

Hello,

I need to create a calculated field in a query that calculates a commission, based on a particular [manufacturer] and a [dollar amount] sold. If the [dollar amount] is between x and y and the [manufacturer] is A,B,C,D the commission is a fixed amount. Or if the [dollar amount] is >Z and the [manufacturer] is A, B,C,D the commission is a fixed amount. Or if the [dollar amount] is >Z and the [manufacturer] is E,F,G,H the commission is a fixed amount. All three scenarios need to be in the same expression.

I think I need to do something with the iif and in operators, but I haven't been able to come up with the correct expression.

Can someone help?

 

[dcx693]

I would create a custom function for that. You could write an IIf function for it right in the query, but all those parentheses would drive me crazy.

 

[rbrule]

Could you tell me how to do it? I have no clue. I don't know VBA

rbrule

 

[dcx693]

Well, I don't have time to write yours out, but here's a simple example.

Let's say I had two numeric fields in my table, and I wanted to find out what the sum of those two fields would be. Of course, I could just write:
TheSum:[Field1]+[Field2]
in the "Field:" line of my query.

Alternatively, I could write a custom function for it. First, I'll go to the modules tab in the database window. I'll click on New. I'll type the following into the screen:

Function SumTwoNumbers(intNumer1,intNumber2) as Integer
  SumTwoNumbers = intNumber1 + intNumber2
End Function

Then, in my query "Field" line, I'll write this:
TheSum:SumTwoNumbers([Field1],[Field2])

If you choose to go this route, your function will be a bit more complicated than that.

You could, of course, choose to use IIf functions. It's definitely doable, I just hate having tons of nested parentheses. Makes it hard to read.

 

[rbrule]

Thank you, but since I don't know VBA, I'd like to stick to the IIf statement if it is doable. However I do have a couple of questions. How do you note the values in a list? For example [mfg}= A or B or C. I tried this using "or" and "in()", but neither works. Also how would I duplicate the expression for the other two scenarios. I have used "or" between expression #1 and #2, and it doesn't seem to work.

Thank you for your help.

 

[dcx693]

This expression, placed into the "Field:" line of a query, would return "Yes" if the field [Last] is equal to any of "John", "Joe", or "Jim":
IIf(Last] In ("John","Joe","Jim"),"Yes","No")

 

[rbrule]

Thank you, that worked fine.

Is it possible to continue the expression with a different conclusion for different circumstances? If so, how would I do that? I tried reentering the IIf statement after the parentesis using an "or" with different criteria, but it just wiped out the results of the first statement.

 

[dcx693]

You can nest IIf function within each other, and it gets visually difficult to read, but logically it makes sense.

Simple example, if x can only take on the values of 1, 2, or 3:
IIf(x=1,"one",IIf(x=2,"two","three"))

 

[rbrule]

dcx693 thank you so very much! You don't know how much easier you just made my job.

Your example worked perfectly for the problem I had.

rbrule

 

[dcx693]

Cool. You're welcome. Good luck.