Microsoft Access UPDATE or IFF statement

I am trying to write Access SQL statement but without success. :slight_smile:

So, I have Column "Division" and there I have BNW, BNB, WNB etc.
Now I want to create an UPDATE or IFF statement to update the above names as:
BNW = black & white
BNB = black & black
WNB = white & black.

I try with this UPDATE - but not working

UPDATE ForecastMonthly
SET Division = CASE Division
when 'BNW' then 'black & white'
when 'BNB' then 'black & black'
when 'WNB' then 'white & black'


where Division in ('BNW', 'BNB', 'WNB')

Any suggestions or idea how to fix this?
Thank you in advance.

Your sql looks valid.
Whats the error you get?
Is division big enough to hold 13 chars?

Have you tried removing the 'Division' word after the 'CASE' ?

Good idea, although I don't think "remove" on its own will do. Not sure if the SQL Server syntax is unsportrted in Access, but it would be worth trying the alternative syntax:

UPDATE ForecastMonthly 
SET Division = CASE 
when Division = 'BNW' then 'black & white'
when Division = 'BNB' then 'black & black'
when Division = 'WNB' then 'white & black'