Select Case in Query 1

[vr]

Can anyone show me an example of how to put a select case statement in an Access query?

 

[MichaelRed]

I do not think you can do this 'directly'. The closest available query 'statement' would ne a series of nested "IIF"s. You can, of course, have the query call/reference a function which can include any valid VBA statement/function.




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[raskew]

The Switch() function can often be used in a query to emulate a Select Case scenario and eliminate the need for cumbersome nested If() statements.

To demonstrate, open Northwind.mdb then:

(1) Open a new query and copy/paste/run this SQL:

SELECT DISTINCT ShipCountry
FROM Orders
ORDER BY ShipCountry;

(2) You'll be greeted with a list of 21 countries. Now suppose your task was to categorize these countries by continent (NA = North America, SA = South America, EU = Europe). Delete your first query and instead copy/paste/run this SQL. (Note: I renamed ShipCountry to 'SC' just to save space-no special significance.)

 SELECT DISTINCT ShipCountry AS SC, Switch(InStr("Argentina Brazil Venezuela",[SC])>0,"SA",InStr("Canada Mexico USA",[SC])>0,"NA",True,"EU") AS Cont
FROM Orders;

(3) If all worked well, you'll have a new field showing the continent in which each country resides. Now, if either the InStr() function or the Switch() function is new to you, go to the Access help files and read up on them. Pay special attention to the examples.