Case Statement Operator 2

[Delboy14]

Access 2002
I keep getting an error saying that I am missing an operator from this statement does anyone know why?

select Code, case Colour
when Colour = "Blue" then 0;
when Colour ="Green" then 1;
when Colour ="Yellow" then 2;
when Colour ="Red" then 3;
else 4;
end
from Colour;

Thanks in advance

 

[tlbroadbent]

CASE is not in Access. Use the IIF function instead.

select Code, IIF(Colour="Blue", 0, IIF(Colour="Green", 1, IIF(Colour="Yellow", 2, IIF(Colour="Red", 3, 4))))
From Colour;
Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Delboy14]

thanks for that, How can I put these values into a new column in the query, do I INSERT the column then run the query

 

[tlbroadbent]

Add the IIF statement in a field name of the query designer grid.

ColourCd: IIF(Colour="Blue", 0, IIF(Colour="Green", 1, IIF(Colour="Yellow", 2, IIF(Colour="Red", 3, 4)))) Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin