in mysql -> "case n when x1 then y1 when x2 then y2 end"; in ms access -> ?
Posted on 2006-04-02
I'm just curious if this can be done (if yes - how) in MS Access by using SQL. 500pts (grade A) in case of positive answer ;]
In MySQL it looks like this:
when 1 then "one"
when 2 then "two"
when 3 then "three"
end as mod_id
The result will be "one", if id=1; "two", if id=2; "three", if id=3; otherwise - same as value of id.
This is very handy when some "manual" sorting is needed:
select * from table order by case val1 when 'some value' then 0 when 'other value' then 1 when 'another val' then 2 else 3 end;
It's also very handy when you have to change a field in several rows, but do not want to loop through the table several times:
update table1 set id = case id when 1 then 101 when 2 then 102 when 3 then 103 end where id in (1, 2, 3);
Now I tried it in MS Access and found that it does not work. It says 'syntax error (missing operator)' and shows that 'case ... end' expression.
So the question is -- is there any construction in MS Access that works the same way as 'case ... end' in MySQL?