AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

The iif() and switch() functions with two simple conditions

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Using the iif() and switch() functions with two simple conditions

Produce a product catalog with special discounts for one product only

Discussion:

We received an urgent message from the inventory department saying that we have a large quantity of Chocolate Fudge which needs to go out into the market fast.  We only have a few hours to produce a new product catalog for this week and send to our customers.  All of the product prices in the catalog should remain the same except for Chocolate Fudge, which will be reduced by 50%.  We must not make this percentage change right in the table because this price will be valid only for a week. 

Solution 1: using the iif() function

We can solve the problem above using an iif() function.  The translation of the iif() function in this example says:  For the product with productid = 14, change the value of the field ProductUnitPrice by 50%, and for the rest of the products in the catalog, leave the price as is in the table.  In addition, notice that a second function is used, cCur(), to display numbers with the dollar sign in front of them.  Notice also that we changed the title of the ProductUnitPrice field to appear as "ProductPrice" in this week's catalog.  Finally, notice in the result set that the price of Chocolate Fudge is now $20.50 in the query, while it remains $41 in the products table. 

Code:

SELECT ProductID, ProductName, QuantityPerUnit, cCur(iif([productid]=14,[productunitprice]*0.5,[productunitprice])) AS ProductPrice

FROM Products
 

Result:


Solution 2: using the switch() function

There is no need to use the switch function in this case since the conditions are very simple.  However, I would like to show you its syntax and how it works with respect to an iif() function.  In this example, we again used the Ccur() function in front of the switch function to obtain the dollar sign in front of the product prices.  There is no doubt that the switch() function is much cleaner and comprehensible than the iif() function.  This becomes more evident as the number of conditions goes up. 

Code:

SELECT ProductID, ProductName, QuantityPerUnit,

SWITCH(

productid = 14,         productunitprice*0.5,

productid <>14,        productunitprice

)

AS ProductPrice

FROM Products
 

Result: