Introduction
Our tax code is astoundingly complex. It has grown from 400 pages in 1913 to 74,000 pages today.
While there are many reasons
that it is so long, it is
partially because our
government has chosen to have
a complicated tax system to go
along with our complicated
society. I will avoid
addressing criticism toward
the system (that’s for a
different platform than the
Access Wizard); however, take a look at this rule:
RULE: When you buy a bond, you have to pay taxes on the interest payments you receive.
EXCEPTION: You don’t have to
pay taxes on interest from
municipal bonds (because the
federal government wants to
make it easy for local
governments to borrow money).
EXCEPTION TO THE EXCEPTION: If
a municipal bond is used to
finance a professional sports
arena, you have to pay taxes
on the interest (because the
government doesn’t want to
support a sports team).
So we have a rule, an exception to the rule, and then an exception to the exception. This is just a little peek into the complexity embedded in our tax code.
How do software programs like TurboTax address all the complicated rules, procedures, and calculations? Well, they have a series of tools that they throw at the problem backed up by tables and algorithms. The more powerful their techniques, the more complicated cases they can handle.
In this
Wizard, I’m going to give you a very powerful tool to that will
help you when you are faced
with complicated problems: the
Select Case statement on
steroids.
The Simple Select Case
The straightforward select
case approach to avoiding
complicated if statements is
pretty basic. Let’s say that
you work in an animal shelter
and you are trying to analyze
the number of kittens adopted
in each of the last 24 months.
You have decided that you want
to the number adopted each
month put into one of four
buckets, 0–10, 11–20, 21–30,
and > 30
You could write an if statement for this, however, it would be complicated and difficult to debug, especially if you have a lot of potential buckets. With the select case statement, things are considerably clearer.
Select Case KittenCount
Case Is < 11
'Add to Bucket 0 -10
Case Is < 21
'Add to Bucket 11- 20
Case Is < 31
'Add to Bucket 21- 30
Case Else
'Add to Bucket > 30
End Select
This is easy to understand and easy to debug. For a full explanation of this technique, see the
September 2008 Access Wizard.
Select Case on Steroids
Consider the following situation. You are doing an analysis of achievement test scores in grades one through 12. You are interested in treating grades three, six and nine one way, grade 12 another way, and all other grades in a third way.
You could use the same technique as above; it would work just fine, though a bit long. However, we can make even easier by tweaking our select case statement as follows:
Select Case Grade
Case is = 3, 6, 9
‘Put these into
the 3, 6, 9 bucket
Case is = 12
‘Put these into
the 12 bucket
Case else
‘Put these into
the other bucket
End Select
The case statement allows you to evaluate more than a single value for each case. As you can see above, where we say Case is = 3, 6, 9, it evaluates all three cases. Using this technique, we can compress our code and make it easier to read.
However, there is an even more powerful approach with the select case statement.
Say we have the situation as
above, except we want a
special bucket for those
students who have been held
back. It seems this can’t be
handled with the select case
approach, because having
repeated a grade is dissimilar
from the grade that people are
in. However, we can use the
following approach:
Let’s assume that we have a
variable HasBeenHeldBack, and
this is associated with a
student. We simply change our
statement from evaluating a
grade to evaluating whether or
not a certain condition is
true, as follows.
Select Case True
Case HasBeenHeldBack
‘put these into the Held Back
Bucket
Case Grade = 3, 6, 9
‘Put theses into the 3, 6, 9
bucket
Case Grade = 12
‘Put these into the 12 bucket
Case else
‘Put these into the other
bucket
End Select
In this scenario, we are freed from the restrictions of evaluating a single parameter. Instead, we use a much broader and powerful technique where we determine whether or not a particular statement is true. Using this approach, we can handle considerably more complicated situations.
Conclusion
The next time you have a sticky coding problem where your initial reaction is to use an if statement, consider using a select case statements instead. And if it is truly complicated, switch from a direct case statement to the more powerful technique shown here.
Using this approach, you will spend less time solving your coding problems, which will free up time to prepare those complicated tax returns.
