Using 'SWITCH' Command as a Pseudo Case Statement in Access 2007

For a few years now I've bemoaned the lack of a CASE architecture in Access. A recent project has once again illustrated the need for this functionality. My problem resolved around determining if a student was being retained or promoted and assign the appropriate grade level to a column. That's easy enough, we have a flag in the database for an expected promotion. Then the wrinkle comes in, I need to rename the grade levels at the same time. What I needed to accomplish was a calculated column something along the lines of this:

IIf([P]='Y',(IIf([N] In ('KN','KA','KP'),'0',IIf([N]='G','13',[N]))),
(IIf([G] In ('KN','KA','KP'),'0',IIf([G]='G','13',[G]))))

P=Promotion, N=Next Grade Level, G=Current Grade Level

This is a bit more simplistic than my scenario, however the basis is the same. What I basically was attempting to do, as I and many others have done time and time again, is to replicate a CASE statement using a long and complicated string of nested IF statements. Now if you are like me, as the number of nested IF statements grow you will invariably loose track of a parenthesis or two. This is disastrous as you must then work your way through the long string, counting parenthesis and attempting to figure out where in the sequence it is missing from.



So, once again I set out on a walkabout through the internet in search for a better method and what I found astonished me and made me feel foolish for never having seen it before. It was the SWITCH command. This command swaps a variable value with a stated new value. For instance you could do the following:

Switch([G]='06','Sixth') AS Long_Grade

So what this says is evaluate the value in the column and if it is '06' then change it's value to the string 'Sixth' in the query results.

However you can string multiple switch conditions together in the same command such as:

Switch([G]='04','Fourth',[G]='05','Fifth',[G]='06','Sixth') AS Long_Grade

Of course what you can quickly see from this example is that this is analogous to the statement:

CASE [Grade]
WHEN '04' THEN 'Fourth'
WHEN '05' THEN 'Fifth'
WHEN '06' THEN 'Sixth'

So what happened in the end with my project? I ended up with the following. Yes, in the end it is longer. However I don't have to deal with the nested if statements and the stacked up parenthesis.

IIf([P]='Y',

Switch([N] In ('KN','KA','KP'),'0',[N]='G','13',[N] Not In ('KN','KA','KP','G'),[N]),

Switch([G] In ('KN','KA','KP'),'0',[G]='G','13',[G] Not In ('KN','KA','KP','G'),[G]))