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

Get What You Want – The Choose Function


Ted Bundy, the notorious serial killer, died and was greeted at the gates of hell by the Devil. The Devil told Bundy that he must choose between three hells, and the decision, once made, was final.

The first hell was very hot and was filled with people standing on their heads on satin pillows. Bundy saw this and thought this would be horrible – he’d have headaches all the time. “No, I don’t think I like this one,” he said.

The next hell was even hotter. The people in this room were standing on their heads on blocks of concrete. Bundy saw this and thought this would be even worse than the first one. “I think I’ll pass on this one, too.”

The third hell was also hot, but this time Bundy saw people standing in horse manure up to their waist and they looked rather happy. They were all sipping coffee and chatting with each other. Bundy thought to himself, it sure isn’t pleasant with all that horse manure, but at least I won’t be getting any headaches. So Bundy tells the Devil, "I choose this hell.”

The Devil admitted him to the third hell. Bundy then got a cup of coffee and, although not particularly happy, felt rather relieved. Suddenly, he heard a voice from a loud speaker saying, “Attention! Attention! Coffee break is over – back on your heads!”

This joke is a long way around to this month’s topic for the Access Wizard:  the Choose function. The Choose function is a way of taking a numeric value and turning it into virtually anything you want – hopefully something a little bit better than the fate that Bundy encountered in hell.

The Nitty Gritty

The Choose function is fairly straightforward. It takes in a numeric argument, one, two, three, etc., and translates that number into whatever you would like it to be.

We have numeric values in virtually all of our databases and we usually want them to show a related field in that table or in another table. But every once in a while you might have a situation where you want to take a number and turn it into a word on the fly. For instance, rather than “1, 2, 3,” you might want “first, second, third.”

Or we might say that the runner in a race that comes into position number one should get a gold prize, the second place finisher should get a silver prize, etc. With the choose function, it is not hard.
It takes the form Choose (position, value1, value2, ... value n) where position is a whole positive number. It returns the value in the ordinal position corresponding to the position argument. This becomes clearer with an example.

An Example

I have a table in my database that is nothing but a series of numbers 1, 2, 3, 4, 5, etc. Consider these to be places that somebody might finish in a race and we want to translate the finishing spot into the medal that they might receive.

In a new query, I paste the SQL below. (Note If you’re not sure how to paste SQL into a query, take a look at June 2005 Wizard.

SELECT TOP 5 tblCounter.Counter AS Place, Choose([Counter],"Gold","Silver","Bronze","Copper","Clay") AS Medal FROM tblCounter  WHERE (((tblCounter.Counter)<6));

Note that our code up above starts with my field “counter” and that number is translated into gold if it is one, silver if it is two, etc.

When the query is run, here’s what we get:

It is as simple as that. Although you might not use this function frequently, it is something that you will want to have in your toolbox when the need comes about.

Tip of the Month: The Switch Function

This month, we investigated the Choose function, which allows you to take a numeric value and easily translate it into whatever you want. The limitation of this Choose function is that it must start off with a number.

There is a companion function, called the Switch Function, that does not require a number to choose, but rather will switch anything to anything else.

The Switch Function is detailed in the October 2007 Wizard


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

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