Switch Function in a Query?

[jketcher]

Hi,

It was suggested to me that I use the 'switch' function to translate codes from a table/query into meaningful descriptions. I do not know how to do this. Is it done at the table level or can it be done in a query?
eg. 1 = public, 2 = private, 3 = proprietary

Do you have some examples?

Thanks, jketcher

 

[MSAccessRookie]

Hi,

It was suggested to me that I use the 'switch' function to translate codes from a table/query into meaningful descriptions. I do not know how to do this. Is it done at the table level or can it be done in a query?
eg. 1 = public, 2 = private, 3 = proprietary

Do you have some examples?

Thanks, jketcher

The Switch() Command consists of paired sets of Test Cases and Results. The syntax for the command is:

Switch(TestCase1, Result1, TestCase2, Result2, {As many more TestCases and Results as you need...}, DefaultResult)
​

Note that the DefaultResult is an optional parameter and will provide a response in the event that the actual data does not match any of the Test Cases.

 

[jketcher]

How do I set that up in my query. Is it in the SQL view?

 

[raskew]

Hi -

Here's an example using Northwind's Suppliers table:

SELECT Switch(SupplierID= 1, "Alpha", SupplierID =2, "Bravo", True, "Other") as test, Suppliers.Country
FROM Suppliers;

HTH - Bob

 

[gemma-the-husky]

in the query view, though just head a column

columntitle: switch(supplierid = 1, answer1, supplierid=2, answer2, etc)

(i think that is the syntax)

however , if supplierid corresponds to another table (ie a lookup table) then you can just join the tables in the query, and pick up the referenced value.

personally i think that is a better way - if you use switch, and later need to add another option its hard to find all the places you have used switch. if you use a lookup table, you just add another entry to the table

i cant think i have ever used switch

 

[SOS]

i cant think i have ever used switch

Same here - but I think some people get confused over what "lookups" are good and what lookups are evil. They seem to get the impression that they are bad (only if used as a combo/listbox IN the table directly) and not good (used in queries and on forms/reports).

Just my take on it.

 

[jketcher]

Thank you - it worked!