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

Sorting on Steroids - Use the Switch Function

At the 1904 World's Fair, an ice cream vendor ran out of the paper cups he was using as containers. In a lucky move he substituted some thin pastry waffles from a nearby booth, thus inventing the ice cream cone.

In a like way the peanut butter cup (the best candy bar in the world) was invented when two people bumped into each other, one eating a chocolate bar and the other eating peanut butter. The peanut butter mixed with the chocolate and they both realized the combination was a winner.

I know that each of these stories might be stretching the truth a bit, but the main idea here is that sometimes when you put two different things together you get something that's much better than the sum of its parts - "synergy", as the scientists likes to say.

In the last issue I talked about the Switch function and explained how it could be a handy device for creating an on-the- fly method of substituting one word for another word.

Back in the spring of 2006 I spent several issues discussing various approaches to sorting.

This month we'll put these two topics together to demonstrate a very powerful and easy way of sorting.

In this Issue
  • Tip of the Month - Double Click to See Properties
  • Background Info
  • Sorting with the Switch Function - The Details
  • A Caveat and Adjustment
  • Conclusion
  • Trap of the Month - The Case of the Lost Sunken Text Box

  • Background Info

    It might be helpful to review the following specifics about the Switch function before we move forward:

    Switch(expr-1, value-1[, expr-2, value-2 . . . [, expr- n,value-n]])

    If this syntax is baffling, check October 2007 for a full explanation.

    As we discussed last month, the basic purpose of the Switch function is to substitute one value for another.

    Back in May of 06 - Sorting 102 I explained how you could use fields that are not returned to a query as a method for controlling the order of individual items that are returned by a query.

    Sorting with the Switch Function - The Details

    Last month I showed the following query:

    Which, when run shows:

    Let's say we wanted to have dogs show up first in our list, followed by cats, then by birds. If we use the following Switch function we can accomplish our goal:

    SortOnMe: Switch([PetType]="Dog",1,[PetType]="Cat",2, [PetType]="Bird",3)

    SortOnMe is just the label for our column; the rest of it essentially replaces the labels Dog, Cat and Bird with the numbers 1, 2, and 3 respectively. If we put this in the first column and choose ascending we'll get the records ordered first by PetType dog, then cat, then bird.

    A Caveat and Adjustment

    Using the method above we'll get what we want, but there's a problem lurking in our query. By default the Switch function is going to return text rather than numbers. Usually this isn't a problem. However if you go beyond single digits and get up to the number 10, it will be sorted before the number 2 (digits in a column, when sorted as words, are sorted by the first number). And this is just the type of trap that, if you don't fix when you build it, will come back to haunt you. At some point in the future you may find that things are not sorting the way you'd expected, and you'll end up scratching your head. Certainly by that time in the distant future you'll have forgotten your logic in the sorting routine. We can fix this with a small adjustment-adding a conversion function called Cint as follows:

    Cint(Switch([PetType]="Dog",1,[PetType]="Cat",2, [PetType]="Bird",3)).

    This function, which is applied after the records are sorted into the order we specified, will convert the numbers (or integers) to the specified labels (Dog, Cat, and Bird). After running this Cint function, we'll have the sort order and the labels we wanted, and we will have disarmed a time-bomb.


    By adding the Switch function to a query we now have a new tool to put into our toolbox. Employing this extremely powerful technique allows us to sort on multiple values that would typically have no natural sorting characteristics.

    Trap of the Month - The Case of the Lost Sunken Text Box
    Acess Traps

    If you've ever converted a database from an earlier version of Access to Access 2003 or 2007 you may have noticed that your text boxes appeared flat rather than sunken. It looks really weird and is disconcerting.

    What happens when you make this conversion, is that Access sometimes applies themes to your text boxes. To correct this effect do the following:

    Choose Tools>Options from the Main Menu.

    This will bring you to a dialog box were you can change many settings in Access. Click on the Forms/Reports tab, then uncheck the selection that says Use Windows Themed Controls on Forms.

    Once you've done this you'll find that your text boxes have regained their normal sunken look.

    Tip of the Month - Double Click to See Properties

    If you like using a mouse to design forms and reports, you'll frequently want to go to the properties of the control you're working with.

    A common way to set properties is to click on the control, then going to View Menu and choose properties. Or perhaps you've discovered the right click approach and gotten to properties that way.

    There's an even easier way to get to properties - simply double click on the control and the properties box pops right up!

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

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