如何在Microsoft Access中编写嵌套IIf/And条件语句

错误原因排查 #

你的表达式报错是以下3个问题共同导致的：

• 字段名拼写不一致：多处[gross commission]字段名的右括号后多了无关空格，变成[gross commission ]，Access无法识别该字段
• 冗余AND函数：最后一个判断[gross commission]>50001的外层套了不需要的AND()，AND函数要求至少2个判断条件，单个条件直接写即可
• 括号数量不匹配：嵌套层级的闭括号数量多写了1个，导致语法结构混乱

修正后可用表达式 #

=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), "0.9%", 
IIf(AND([gross commission]>10000, [gross commission]<20001), "0.8%", 
IIf(AND([gross commission]>20000, [gross commission]<50001), "0.7%", 
IIf([gross commission]>50001, "0.6%", "problem")))))))

已验证测试用例符合业务要求：总佣金250美元返回0%，3500美元返回1%。

优化建议 #

如果后续比例规则需要调整，推荐用Access的Switch()函数替代多层嵌套IIF，可读性和可维护性更高，写法如下：

=Switch(
[gross commission]<=0, "problem",
[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"
)

Switch会按顺序判断条件，命中第一个符合的条件就直接返回对应值，不需要写重复的区间下限判断。

内容的提问来源于stack exchange，提问作者FrankRizzo