|
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.
| 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.
|
| Conclusion |
|
|
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 |
|
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!
|
|
