Operator Error with SWITCH Function in Access

Question

I have been trying to execute the below SWITCH function in MS Access but I keep receiving the following error:

Syntax error (missing operator) in query expression.

I have used this before and it worked fine, but I have since received an error.

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;

Lee Mac · Accepted Answer · 2019-12-24 17:13:36Z

As explained by Olivier, the syntax error arises because the second comparison operator is missing the field on the left-hand side of the comparison:

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

However, note that only one comparison is actually required and therefore the code could be reduced to the following:

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

Since, if netsales is less than 1000, the first test expression will always be validated before the second, and so on.

Olivier Jacot-Descombes · Accepted Answer · 2019-12-24 16:27:38Z

1

[netSales] >=1000 AND <2000 has not the correct syntax. You have to repeat the column name after AND. Write

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

(same for the other lines.)

answered Dec 24, 2019 at 16:27

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Collectives™ on Stack Overflow

Operator Error with SWITCH Function in Access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related