Microsoft Access UPDATE or IFF statement

Other Tech Topics
1

Hello,
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'

END

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

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

2

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

3

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

4

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'