IF statement Help please

[Zonie32]

I am trying to calculate a fee. Here is what I have that works so far, but I need to add more criteria. Not sure how to write it.

This works fine. Now I want to say IF [Product]=anything other than MST, add 2 to the [Provider fee].

So basically if Product = MST add 3, any other Product only add 2.

Can anyone help me finish this statement? Thanks.

IIf([PPO ID]="3910" And [Product]="MST",Sum([Provider Fee]+3)-[Ancillary Fee]-[PEPM])

 

[dhookom]

Try:
Fee:IIf([Product]="MST", 3,2)
You can add this expression to any other field or value.

I have a tendency to store the "2" or "3" in the products table. This allows me to easily assign 2 or 3 or even some other value as needed. I won't have to go back and modify expressions in queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Zonie32]

Thanks for the reply. I still cannot get it working properly.

Here is what I have - the error is wrong number of arguments.

NewProvFee: IIf([PPO ID]="3910" And [Product]="MST",[Provider Fee]+3,+2,[Provider Fee]))

I am trying to add $3 dollars to the Provider Fee if the Product is MST and only add $2 dollars to the Provider Fee if the product for this specific PPO ID is all others,ie CCP, CGA, MGA, PPS, GAS.

 

[earthandfire]

Try:

NewProvFee: IIf([PPO ID]="3910" And [Product]="MST",[Provider Fee]+3,[Provider Fee]+2)

Hope this helps.

 

[Zonie32]

well, earthandfire, this does work, however it is adding $2 to all of my other PPO ID's that have those same Products. How can I get it to add the $2 dollars to only PPO ID 3910??

 

[earthandfire]

Try this:

NewProvFee: IIf([PPO ID]="3910" And [Product]="MST",[Provider Fee]+3,IIf([PPO ID]="3910",[Provider Fee]+2,[ProviderFee]))


Hope this helps.

 

[dhookom]

What happens when you want to add another exception to your logic? I expect you would need to modify expressions in queries and elsewhere.

At the very least, this calculation should be conducted in a user-defined function that returns the correct value based on current business rules. If I was creating this in an application for a client/customer, the 2s and 3s would be in tables where the client/customer could edit the values and wouldn't have to contact me to change the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

NewProvFee: ProviderFee + IIf([PPO ID]="3910", 2+Abs([Product]="MST"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zonie32]

I do have another exception to this expression. If the PPOID is 3910 and the product is CNB or EMP, then I don't want to add anything to the Provider Fee.

Here are the Provider Fees in the fee table.
MST 12.00
CCP 13.00
CNB 0.00
EMP 0.00
MGA 11.00
CGA 11.00
PPS 11.00

So the expression below works, but it is adding $2 to CNB and EMP, which I need those two products to remain at $0. How can I write that in there?

NewProvFee: IIf([PPO ID]="3910" And [Product]="MST",[Provider Fee]+3,IIf([PPO ID]="3910",[Provider Fee]+2,[ProviderFee]))

 

[earthandfire]

Are the provider fees different for different products?

 

[PHV]

NewProvFee: ProviderFee + IIf([ProviderFee]<>0 And [PPO ID]="3910", 2+Abs([Product]="MST"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zonie32]

the fees I have listed are the fees for each product. each product has a different fee that we charge the PPO.

For this particular PPO 3910, we are charging them $3 more for the MST product and $2 more for the others, excluding CNB and EMP, which we don't charge anything. otherwise all other PPO's we charge the regular prices that are listed in the fee table.

 

[Zonie32]

PHV,

Your expression works just great! Not sure what it all means, but it works. Thanks to all of you for getting me on the right track.