Introduction
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.
