Access中有多个嵌套的iif语句

Question

我正在尝试计算员工使用其服务年限有权享受的假期周数，包括以下类别：

0-4 years = 2 weeks
5-14 years = 3 weeks
15-24 years = 4 weeks
25 and up = 5 weeks

我找到了一些例子，并尝试执行嵌套的iif语句，但不断收到错误消息。有人能告诉我我做错了什么吗？

VacationWeeks: IIf([YearsInService]<=4, "2",
IIf([YearsInService]is between 5 and 14, "3",
IIf([YearsInService]is between 15 and 24, "4",
IIf([YearsInService]>=25, "5", ”0”))))

Answer 1

您可能因为代码的两个问题而收到错误消息：

1. BETWEEN语句的语法为：

   expr [Not] Between value1 And value2
   

   您应该而不是包含单词是：

   IIf([YearsInService] is between 5 and 14,
   ^-------------------- this shouldn't be here
   

2. 最后一个else参数将字符串包含在"智能引号"或Unicode字符0x201C:中

   IIf
   (
   [YearsInService] >= 25,
   "5",
   ”0” <--- here
   )
   

   与标准的双引号相反，双引号是Unicode/ASCII字符0x0022:

   IIf
   (
   [YearsInService]>=25,
   "5",
   "0"
   )
   

纠正这两个问题会产生IIF语句，例如：

IIf
(
[YearsInService] <= 4,
"2",
IIf
(
[YearsInService] between 5 and 14,
"3",
IIf
(
[YearsInService] between 15 and 24,
"4",
IIf
(
[YearsInService] >= 25,
"5",
"0"
)
)
)
)

然而，您可能会发现SWITCH语句的使用更可读：

Switch
(
[YearsInService] <=  4, "2",
[YearsInService] <= 14, "3",
[YearsInService] <= 24, "4",
[YearsInService] >  24, "5",
True, "0"
)

最后一个测试表达式提供了一个catch-all默认值来解释null，并且仍然返回一个字符串值——我将让您根据应用程序的要求来决定是要包含还是省略它。