接入网中开关函数的操作错误

Question

我一直试图在MS Access中执行以下SWITCH函数，但我一直收到以下错误：

查询表达式中的

语法错误(缺少运算符)。

我以前使用过它，它运行得很好，但是后来我收到了一个错误。

SELECT tempJoin.[cardID], tempJoin.[netSales], tempJoin.[amountRedeemed],
SWITCH (
    [netSales] <1000, "0-1000",                     
    [netSales] >=1000 AND <2000, "1000-2000",         
    [netSales] >=2000 AND <3000, "2000-3000",        
    [netSales] >=3000 AND <4000, "3000-4000",       
    [netSales] >=4000 AND <5000, "4000-5000",        
    [netSales] >=5000 AND <6000, "5000,6000",        
    [netSales] >=6000 AND <7000, "6000-7000",       
    [netSales] >=7000 AND <8000, "7000-8000",       
    [netSales] >=8000 AND <9000, "8000-9000",        
    [netSales] >=9000 AND <10000, "9000-10000",      
    [netSales] >=10000, ">10000"
    ) AS netSalesBin
    INTO tempJoin
FROM tempJoin;

Answer 1

正如Olivier所解释的，语法错误的出现是因为第二个比较运算符缺少比较左侧的字段：

[netSales] >= 1000 AND ??? < 2000 
                        ^--------------- Missing field

但是，请注意，实际上只需要进行一次比较，因此可以将代码简化为：

select 
    tempjoin.[cardid], 
    tempjoin.[netsales],
    tempjoin.[amountredeemed],
    switch (
        [netsales] <  1000, "0-1000",
        [netsales] <  2000, "1000-2000",
        [netsales] <  3000, "2000-3000",
        [netsales] <  4000, "3000-4000",
        [netsales] <  5000, "4000-5000",
        [netsales] <  6000, "5000,6000",
        [netsales] <  7000, "6000-7000",
        [netsales] <  8000, "7000-8000",
        [netsales] <  9000, "8000-9000",
        [netsales] < 10000, "9000-10000",
        true, ">10000"
    ) as netsalesbin
into tempjoin
from tempjoin

因为，如果netsales小于1000，那么第一个测试表达式总是在第二个测试表达式之前被验证，依此类推。

Answer 2

[netSales] >=1000 AND <2000没有正确的语法。您必须在AND之后重复列名。写

[netSales] >=1000 AND [netSales] < 2000

(其他行也一样。)