The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
October 2007

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 costume parties.

Along this theme, we take a look at a function that allows one thing to appear to be something different -- the Switch function.

In this Issue
  • Tip of the Month - Super fine movement of a control
  • The Use of the Switch Function
  • Syntax - and I'm not talking about your local Government Raising Money
  • Application
  • Pushing the Envelope
  • Caveats, Alternatives, and Conclusion

  • 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, and Avian.

    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 . . . [, expr-n,value-n]])

    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:

    Switch([PetType]="Dog","Canine",[PetType] ="Cat","Feline",[PetType]="Bird","Avian")

    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 trailing parenthesis.

    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.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!


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