How to do a nested If / and statement in Microsoft Access?-CodePudding

This is easy to do in excel, however, I can't figure out how to do the same thing in Access and need to do it in Access for reasons.

I have a table that determines a commission payout percentage based on the sale amount. So if a sale is $250 the payout is 0%. If it's $3500, the payout is 1%. It's all in one huge table. See below example

=iif(AND([gross commission]>0,[gross commission]<501),"0%",iif(AND([gross commission ]>500,[gross commission ]<1501),"1.25%",iif(AND([gross commission]>1500,[gross commission ]<5001),"1%",iif(AND([gross commission ]>5000,[gross commission]<10001),".9%",iif(AND([gross commission ]>10000,[gross commission ]<20001),".8%",iif(AND([gross commission]>20000,[gross commission ]<50001),".7%",iif(AND([gross commission ]>50001),".6%","problem"))))))))

I get an invalid syntax error, "you may have entered a comma w/o a preceding value or identifier". I haven't had a Google search result that deals with this type of nested formula.

Thanks very much in advance for all suggestions

CodePudding user response：

Nothing extraordinary about your requirement. The IIf() pattern still applies:
IIf(conditional expression here, do this if true, else do this if false).
Access syntax for your requirement would be:
=IIf([gross commission]>0 AND [gross commission]<501, "0%", IIf(... )))))))

However, the AND compound criteria is not necessary.

Consider:

=IIf([gross commission]<501,"0%", 
IIf([gross commission]<1501,"1.25%", 
IIf([gross commission]<5001,"1%", 
IIf([gross commission]<10001,"0.9%", 
IIf([gross commission]<20001,"0.8%", 
IIf([gross commission]<50001,"0.7%", 
IIf([gross commission]>=50001,"0.6%","problem")))))))

Or:

=Switch([gross commission]<501,"0%", 
[gross commission]<1501,"1.25%", 
[gross commission]<5001,"1%", 
[gross commission]<10001),"0.9%", 
[gross commission]<20001),"0.8%", 
[gross commission]<50001),"0.7%", 
[gross commission]>=50001,"0.6%", 
True, "problem")