SQL CASE Statement in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. IIF

IFF:IIf([Number]<=100, "(0,100]"
,IIf( [Number]<=200,"(100-200]",
IIf([Number]<=300,"(200-300]",
IIf([Number]<=400,"(300-400]",
IIf([Number]<=500,"(400-500]",
IIf([Number]<=600,"(500-600]",
IIf([Number]<=700,"(600-700]",            
IIf([Number]<=800,"(700-800]",              
IIf([Number]<=900,"(800-900]",
IIf([Number]<=1000,"(900-1000]",
IIf([Number]>1000,">1000",""
)))))))))))

We use nested IIF statements to create the intervals. We need to be very careful to include all parentheses.
2. SWITCH

SWTICH:
Switch([Number]<=100,"(0,100]"
,[Number]<=200,"(100-200]"
,[Number]<=300,"(200-300]"
,[Number]<=400,"(300-400]"
,[Number]<=500,"(400-500]"
,[Number]<=600,"(500-600]"
,[Number]<=700,"(600-700]"
,[Number]<=800,"(700-800]"
,[Number]<=900,"(800-900]"
,[Number]<=1000,"(900-1000]"
,[Number]>1000,">1000")

SWITCH has a cleaner syntax. We avoid nesting all conditions using pairs of expressions and values.

References

• IIF
• SWITCH

Related posts

• SQL CASE Statement using an intermediate table in Ms Access