Updating a Query with SQL in Access 2013

Question

I am trying to update a Query in Microsoft Access using SQL. I am getting the following error when I run "Syntax error (missing operator) in query expression" I've added the following code to bottom of my basic SELECT/FROM Query script.

UPDATE [HRBI Query]
SET [HRBI Query].[PaySegmentMultiplier] = (CASE WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment' THEN 0 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1' THEN 1.35 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1' THEN 1.25 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2' THEN 1.15 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3' THEN .90 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4' THEN .60 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5' THEN .40 ELSE CASE
ELSE PaySegmentMultiplier
END
END
END
END
END
END) 
FROM [HRBI Query];

I am new to SQL. Can anyone explain why I am receiving this error and how to fix? Thanks.

EDIT:

I tried using SWITCH, but am still receiving an error on sytax. Any ideas?

SELECT SWITCH([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment',[HRBI Query].PaySegmentMultiplier = 0,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', [HRBI Query].PaySegmentMultiplier =1.35,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', [HRBI Query].PaySegmentMultiplier =1.25,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', [HRBI Query].PaySegmentMultiplier =1.15,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', [HRBI Query].PaySegmentMultiplier =.90,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', [HRBI Query].PaySegmentMultiplier =.60,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', [HRBI Query].PaySegmentMultiplier =.40, True,'Error') 
FROM [HRBI Query];

EDIT: I tried this and still syntax error?

SET [HRBI Query].[PaySegmentMultiplier] = Switch (
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment', 0, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', 1.35, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', 1.25, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', 1.15, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', .90, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', .60, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', .40, TRUE, PaySegmentMultiplier);

don't think you need else case... change them to just have 'when'.. but I thought access didn't support case in SQL... perhaps `switch` or `iif`? http://stackoverflow.com/questions/14785586/what-is-the-correct-case-select-statement-in-access-2010 — xQbert, Sep 04 '15 at 20:05
You have `[HRBI Query].PaySegmentMultiplier = 0` where a value `0` would belong. See the example below. — Andre, Sep 04 '15 at 20:46
Thanks Andre451. I tried Switch but am still getting syntax error? Does it make a difference that I am adding the SET to the bottom of the main Query code. (SELECT/FROM)? — Chris2015, Sep 04 '15 at 20:54
SET doesn't go together with SELECT FROM. SET is for UPDATE queries. I edited my answer. — Andre, Sep 04 '15 at 20:59

Andre · Answer 1 · 2015-09-04T20:55:42.933

CASE WHEN .. ELSE .. END is for Sql Server.

In Access you use the Switch function:

Switch ( expression1, value1, expression2, value2, ... expression_n, value_n )

so in your case

UPDATE [HRBI Query]
SET [HRBI Query].[PaySegmentMultiplier] = Switch (
[PayGroupCountryDesc] = 'France'  AND BasePayRangeSegment = 'Above top segment', 0, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', 1.35,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', 1.25
[...etc...]
[and to simulate ELSE]
True, PaySegmentMultiplier
)

or for a SELECT query:

SELECT Switch ( ... ) AS NewPaySegmentMultiplier FROM [HRBI Query]

score 0 · Accepted Answer · answered Sep 04 '15 at 22:52

In MS Access, the best translation of CASE/WHEN would be the nested IIF() function. Also in update SQL statements with Access' dialect, you would not use the FROM clause at end.

The only challenge is keeping track of parentheses which should equal the number of IIFs. I indent below to help visualize:

UPDATE [HRBI Query]
SET [HRBI Query].[PaySegmentMultiplier] = 
IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment', 0,
   IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', 1.35,
      IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', 1.25,
        IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', 1.15,
           IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', .90, 
              IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', .60, 
                 IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', .40, 
                     PaySegmentMultiplier
                 )
              )
           )
        )
     )
  )
);

Updating a Query with SQL in Access 2013

2 Answers2

Linked