Inside This ArticleIIF function basics - the CASE statement of MS Access
IIF function is used to make IF...THEN....ELSE logic processing in Access queries. If you have
used sql in a DBMS (Database Management System) such as Oracle, SQL Server, or MySQL, you
may be familiar with CASE statement. IIF is MS Access' equivalent of CASE
statement that you normally can find in enterprise level database server
environment.
Code example 1:
For example, in Northwind Tradders Products table, we rank the product price as High or Low by
making the following logic checks. If Unit Price is greater than $100, it's classified as High Price. Otherwise, it is Low Price.
| Query Name |
Qry_Func_IIF_1 |
| Logic |
IF...THEN...ELSE... |
| IIF Code |
IIF(UnitPrice > 100, "High Price", "Low Price") |
| Design view |
 |
| SQL View |
SELECT Products.ProductID,
IIf([UnitPrice]>100,"High Price","Low Price") AS [Price Level]
FROM Products;
|
| Datesheet View |
77 records returned:
 |
Tip: To test out the query, copy and paste the SQL into SQL view in Access.
Please note that IIF is not case-sensitive, you can write it as iif, Iif, IIF...
whatever way you want.
Code example 2:
If we have more than two conditions to test, you can embed one IIF in another
IIF statement to achieve the expected result.
For example, in Orders table, we can rank the Freight as follows:
- High - greater than $200
- Medium - between $100 and $200, inclusive.
- Low - lower than $100
| Query Name |
Qry_Func_IIF_2 |
| IIF Code |
IIf(Freight>200,"High",IIf(Freight>=100 and
Freight<=200,"Medium","Low")) |
| Design View |
 |
| SQL View |
SELECT Orders.OrderID,
Orders.Freight,
IIf(Freight>200,"High",
IIf(Freight>=100 And Freight<=200,"Medium","Low"))
AS [Freight Price Level]
FROM Orders;
|
| Datasheet View |
830 records returned:
 |
To sum up, here we have illustrated how to use IIF function in MS Access SELECT statement to conditionally test or select
records in Access database table. Part 2 of IIF function will show you how to use
IIF function in GROUP BY clause for more funs.
Copyright© GeeksEngine.com
|
Inside This Article
Related Articles:
Other Recent Articles from the MS Access category:
|
