在访问中更新查询 - 何时更新查询

Question

我对SQL代码很陌生，仍在学习。

我有一个表格Gas_Data其中，自某个事件(即 DATEDIFF(以来我有几天的时间，我需要对此进行年龄分级，并且我正在努力编写所需的编码。

我尝试创建另一个表作为参考，并从示例中尝试了各种不同的编码。

"查询表达式'CASE"中的语法错误(缺少运算符(

UPDATE Gas_Data
SET MONTH_BAND =
CASE
WHEN DATEDIFF < 29 THEN '00. Up to 1 Month'
WHEN DATEDIFF BETWEEN 30 AND 60 THEN '01. 1 to 2 Months'
WHEN DATEDIFF BETWEEN 61 AND 60 THEN '02. 2 to 3 Months' 
WHEN DATEDIFF BETWEEN 91 AND 60 THEN '03. 3 to 4 Months' 
WHEN DATEDIFF BETWEEN 121 AND 60 THEN '04. 4 to 5 Months' 
WHEN DATEDIFF BETWEEN 151 AND 60 THEN '05. 5 to 6 Months' 
WHEN DATEDIFF BETWEEN 181 AND 60 THEN '06. 6 to 7 Months' 
WHEN DATEDIFF BETWEEN 211 AND 60 THEN '07. 7 to 8 Months' 
WHEN DATEDIFF BETWEEN 241 AND 60 THEN '08. 8 to 9 Months' 
WHEN DATEDIFF BETWEEN 271 AND 60 THEN '09. 9 to 10 Months' 
WHEN DATEDIFF BETWEEN 301 AND 60 THEN '10. 10 to 11 Months' 
WHEN DATEDIFF BETWEEN 331 AND 60 THEN '11. 11 to 12 Months' 
WHEN DATEDIFF BETWEEN 366 AND 60 THEN '12. 12 to 18 Months' 
WHEN DATEDIFF BETWEEN 546 AND 60 THEN '13. 18 to 24 Months' 
WHEN DATEDIFF BETWEEN 731 AND 60 THEN '14. 24 to 30 Months' 
WHEN DATEDIFF BETWEEN 911 AND 60 THEN '15. 30 to 36 Months' 
WHEN DATEDIFF >= 1096 THEN '16. > 36 Months'
ELSE 'NO_AGE_BAND' END AS UNBILLED_MONTHS 
END

MONTH_BAND
00. Up to 1 Month
01. 1 to 2 Months
02. 2 to 3 Months
03. 3 to 4 Months
04. 4 to 5 Months
05. 5 to 6 Months
06. 6 to 7 Months
07. 7 to 8 Months
08. 8 to 9 Months
09. 9 to 10 Months
11. 11 to 12 Months
12. 12 to 18 Months
13. 18 to 24 Months
14. 24 to 30 Months
15. 30 to 36 Months
16. > 36 Months

Answer 1

MS Access 改用switch()：

UPDATE Gas_Data
    SET MONTH_BAND = SWITCH(DATEDIFF < 29, "00. Up to 1 Month"
                            DATEDIFF BETWEEN 30 AND 60, "01. 1 to 2 Months"
                            . . .
                            DATEDIFF >= 1096, "16. > 36 Months"
                            1=1, "NO_AGE_BAND");

MS Access 还首选使用双引号作为字符串分隔符。