The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

The Select Case Statement on Steroids


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.


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. 

Tip of the Month: How to Get the Current Database Name and Path Easily 

When you are working with an Access database, it isn’t always obvious where the database lives on your hard drive or network. This is especially true when you open the database from a link on your desktop.

You can get the name and path easily by going to the code window and then going to the immediate window. To go there with a shortcut, press and hold the control key and then press and release the G key. Once you are in the immediate window, type the following:

? Currentdb.Name

This will give you the full path and name of your database.
If you just want path, you can use

? CurrentProject.path
Through these two objects, Currentdb and CurrentProject, you have other interesting pieces of information available to you. Type them into your immediate window followed by a period and you will see the list of possibilities.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886