How to fix syntax error in SWITCH function

Question

I am attempting to fill a column labeled type_match depending on the content of two columns 2018_status and 2019_status in the status_report table.

All three columns are short text. I keep getting a 'syntax error'.

UPDATE status_report AS per  
SET per.type_match = 
Switch(
   per.2018_status = 'No application found', 'No application for 2018',
   per.2019_status = 'No application found', 'NA',
   per.2018_status = per.2019_status, 'Yes',
   True, 'No'
)

The documentation seems pretty straightforward and believe it's an MS Access issue where they tend to be very picky.

True is totally valid.

forpas
– forpas

2019-07-05 14:03:47 +00:00
Commented Jul 5, 2019 at 14:03 — forpas
– forpas, Commented Jul 5, 2019 at 14:03

forpas · Accepted Answer · 2019-07-05 14:12:59Z

1

There is nothing wrong with Switch, you only need to enclose in square brackets the column names that have numbers at the start:

UPDATE status_report AS per  
SET per.type_match = 
Switch(
   per.[2018_status] = 'No application found', 'No application for 2018',
   per.[2019_status] = 'No application found', 'NA',
   per.[2018_status] = per.[2019_status], 'Yes',
   True, 'No'
)

edited Jul 5, 2019 at 14:12

answered Jul 5, 2019 at 14:07

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

honey_badgerzz Over a year ago

Wow that totally fixed it, I never thought that'd be an issue since I've never enclosed column names with square brackets

forpas Over a year ago

Column or table names starting with numbers, or containing spaces, or reserved words, all must be enclosed in square brackets.

Gordon Linoff · Accepted Answer · 2019-07-05 13:56:17Z

0

You can use 1=1 for the default:

UPDATE status_report AS per  
    SET per.type_match = Switch(per.2018_status = "No application found", "No application for 2018,
                                per.2019_status = "No application found", "NA",
                                per.2018_status = per.2019_status, "Yes",
                                1=1, "No"
                         );

MS Access also traditionally uses double quotes for strings, so I changed them as well.

answered Jul 5, 2019 at 13:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to fix syntax error in SWITCH function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related