Halloween - Time to Switch to Something Different
October is one of my favorite times of the year. The
leaves are turning, the air is crisp, we get fresh apple
cider, and it's Halloween time.
One of the neatest things about Halloween is that kids
and adults alike have the opportunity to become
something they aren't: kids in costume as trick-or-
treaters on Halloween night and adults attending
Along this theme, we take a look at a function that
allows one thing to appear to be something different --
the Switch function.
The Use of the Switch Function
The little used Switch function can be very handy when
you need it. Let's say that you have a database that
deals with pets. You have tables that describe the
pets: general type of animal (dog, cat, bird), Latin
name for the animal, size, characteristics,
temperament, feeding habits, etc.
You've classified each pet with the general type of
animal that people associate them with, for example
German Shepards and Poodles are classified as
dogs; Manx and Russian Blues are classified as cats;
Cockatiels and Budgies are classified as birds.
Let's say you are going to make a presentation to a
sophisticated customer, perhaps researchers at an
academic institution. Rather than use pedestrian
terms, likes dogs, cats and birds as types of pets, you
want to impress them with your knowledge by
referring to the animals by their family name in the
organism classification system, e.g. Canine, Feline,
You could set up a separate table to handle this or you
could put a new field in a table, but let's say that this is
a once in a blue moon situation and you just want to
assign the labels without making any structural
changes to the database.
This is where the Switch function shines. It can be
used to take any one word or phrase and switch it to a
different word or phrase.
Syntax - and I'm not talking about your local Government Raising Money
The syntax of the Switch function, taken directly from
Access Help, is as follows:
Switch(expr-1, value-1[, expr-2, value-2 . . . [,
It's a typical Microsoft help function because it looks
like mud; but let's dissect and explain it. You start with
the word Switch and then follow with an open
parenthesis. After that you must have an expression --
in this case it would be the name of the field that
contains the data you want to swap out, followed by
the value that you want to replace it with.
Notice that you may include multiple expressions
within the same Switch function.
Again this is all rather abstract, so let's illustrate this
with an example.
Let's say that you have the following table
Our goal is to create a query that shows Feline
instead of Cat, Canine instead of Dog, and Avian
instead of Bird.
Create a new query like the following:
The full Switch function (which is cut off in the
screenshot above) is:
As you can see we start with the word Switch and then
in parenthesis we have our field name [PetType], the
equals sign and then the value we want to switch (in
this case "Dog") and what we want to switch it to (in
this case "Canine").
We continue that process for every instance of a pet
type that we want to rename, and then we close with a
When we run this query we get:
Which is exactly what we're looking for.
Pushing the Envelope
Because the function uses an expressions, e.g.
[PetType]="Dog", we can make use of this to do
interesting things. It's essentially an "if" statement,
only in this case it returns something only if the
condition is true. If it's false, it returns nothing.
What this means is that you can use it for
mathematics, comparisons, Boolean logic, anything
that results in a true or false answer. Further, what it
returns is also an expression. In our case we were
just swapping out to another word, but we could
concatenate (put more than one thing together), do
math, or anything else that is logical.
What's neat about the Switch function is that it's
limited only by your imagination and requires no
structural changes to your database
Caveats, Alternatives, and Conclusion
The Switch function is convenient, but clumsy if you're
trying to apply it to more than two or three terms.
If you have a bunch of terms you need to manipulate,
you're better off adding a field to your table or creating
a mapping table to handle the translation. You should
also be aware that there's a function called Immediate
If which returns one value if an expression is true and
another value if an expression is false - the
Immediate If function is also very powerful and handy;
it's something I'll cover in a future Wizard.
But know that when you need to quickly transform one
value or set of values to another, the Switch function
can get you there quickly
Tip of the Month - Super fine movement of a control
Last month I gave you a tip on how to easily move a
textbox just a bit by using the Control and cursor keys.
And that's a great way to move a textbox, or other
control, around a form or report. However, sometimes
you need even finer control over a textbox's position.
You can move it by just one pixel at a time by taking an
entirely different approach.
When you're in the design view of a form or report,
choose the textbox or other control that you want to
move and go to its properties view (Right click |
Properties). On the format tab, you'll see that there is a
left and top property. You can type in whatever you'd
like there. Using this technique you may adjust a
control very precisely so that it sits just where you'd
like it to be.