Conditional Expressions in JET SQL

AKA “Can I use If … Then or Select … Case in a Microsoft Access query?”

Part Four

As the title of this series indicates, the original intention of the series was to explain the use of conditional expressions in JET SQL. We’ve strayed somewhat from that original intention, largely because some of the issues raised appear to be undocumented (or if they are documented, said documentation seems to be remarkably well hidden) necessitating a lot more trial-and-error investigation than I’d originally expected.

In today’s post, I’m going to return to the original subject, and examine one of the conditional functions that can be used in JET queries, both within and outside of the Microsoft Access environment, namely the Choose function.

The Choose function takes a variable number of arguments. The first argument (referred to as “index” in the help files) is a numeric expression. The subsequent arguments (referred to as “choice-1”, “choice-2”, etcetera in the help files) form a list of possible values that the function may return. The value returned is the value of the argument in the position within the list that matches the value of the “index” argument. In other words, if the “index” argument evaluates to the number 1, the function will return the value of the first argument in the list following the “index” argument. If the “index” argument evaluates to the number 2, the value of the second argument in the “choice” list is returned, etcetera.

If the value of the “index” argument is not a whole number, it is rounded to the nearest whole number before being evaluated.

Clear as mud, right? Perhaps an example might help.

The “Northwind” sample database that comes with Microsoft Access includes a table, “Shippers”, with an AutoNumber primary key field (“ShipperID”) and a text field, “CompanyName”, that stores the names of shipping companies. It contains just three records, with the following values:

ShipperID	CompanyName
1	Speedy Express
2	United Package
3	Federal Shipping

The “Orders” table includes a foreign key field, “ShipVia” that contains values from the “ShipperID” field in the “Shippers” table, i.e. the “ShipperID” of the shipping company that shipped that order.

If you look at the “Orders” table in datasheet view in Microsoft Access, you’ll see the name of the shipping company rather than the “ShipperID”, because the field is defined as a “lookup field”. But this is an Access-specific feature and even within Microsoft Access it controls only what is displayed on the screen. What is actually stored in the “Orders” table is the numeric value or the ShipperID field, not the textual company name that you see displayed. If we were to define a query such as “SELECT ShipVia FROM Orders” and display the results of that query outside of Microsoft Access, in a Microsoft Excel worksheet, a Microsoft Word document, or our very own “JetTest” utility, we’d get the numeric “ShipperID” values, not the company names.

One way to get the shipping company names along with the order details is to join the two tables in a query such as the following …

SELECT Orders.OrderID, Orders.OrderDate, Shippers.CompanyName AS Shipper FROM Shippers INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia;

In most circumstances, this is likely to be the best and most efficient way to retrieve this information. But as an example of how to use the Choose function, here’s how we can retrieve the same information using the Choose function and without the join …

SELECT OrderID, OrderDate, Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping") AS Shipper FROM Orders;

If the “ShipVia” field contains the value 1, the Choose function will return the first item in the list of choices, “Speedy Express”. If the “ShipVia” field contains the value 2, the Choose function will return the second item in the list of choices, “United Package”. And if the “ShipVia” field contains the value 3, the Choose function will return the third item in the list of choices, “Federal Shipping”.

If the value of the “index” argument (the “ShipVia” field in the example above) is less than 1 or greater than the number of items in the list of choices, the Choice function returns Null (a special value indicating a missing or unknown value, which displays as an empty field in the Microsoft Access datasheet view). So in our example, if the “ShipVia” field contained a number less than 1 or a number greater than 3, the “Shipper” column in the query would contain Null. If the “ShipVia” field itself contained a Null value, that would cause an error. The “index” argument must evaluate to a numeric value, and Null is not a number. At the expense of introducing a “forward reference” (something that won’t be explained until later) here’s how we could avoid such errors, using the IIf() function …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping")) AS Shipper FROM Orders;

While the Choose function returns the value of only one of the “choice” arguments, it evaluates all of them. This is of little consequence when the arguments are simple string literals as in our example, but can have “side effects” when the arguments are more complex expressions, perhaps including calls to other functions. Take the following example, where the “choice” arguments are calls to a custom VBA function ("MyMsgBox") that in turn calls the built-in MsgBox method …

The query …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], MyMsgBox("Speedy Express"), MyMsgBox("United Package"), MyMsgBox("Federal Shipping"))) AS Shipper FROM Orders WHERE OrderID = 10248;

The VBA function …

Public Function MyMsgBox(ByVal TheMessage As String) As String

    MsgBox TheMessage
    MyMsgBox = TheMessage

End Function

While this will return the correct value in the query, it will display three message boxes, one for each of the three items in the list of choices. That’s why I added the criteria (“WHERE OrderID = 10248”) to limit the result to just one record – I didn’t feel like clicking through three message boxes for every record in the Orders table!

If you came here looking for information about using conditional expressions in JET SQL, I hope this post goes some way toward making up for the diversions from that subject in previous posts. If, on the other hand, you were actually more interested in some of the other subjects, don’t worry – I’ll be returning to those in subsequent posts.

Future plans include (as always, in no particular order and in no specific time-frame) …

Continue the investigation into which functions are available in JET SQL when used outside the Microsoft Access environment. Explain the syntax and use of the other conditional functions, IIf and Switch. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the utility for those who may not be familiar with VS.NET.

Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.

# posted by Brendan Reynolds @ 11:09 AM