Friday, May 07, 2004

 

Functions in Microsoft Jet Queries

The IIf (Immediate If) Function

The IIf function takes three arguments, referred to in the help files as “expr”, “truepart”, and “falsepart”. The function returns the value of the “truepart” argument if the “expr” (expression) argument evaluates to True. It returns the value of the “falsepart” argument if the “expr” argument evaluates to False.

For example, suppose we have a field (TestText in this example) in a table (tblTest in this example) that may contain Null values. What we’d like to display to the user is the value of the field if it is not Null, or the text “N/A” if the value of the field is Null. The following query will achieve this goal:

SELECT IIf([TestText] Is Null,"N/A",[TestText]) AS TestIIf FROM tblTest;

Microsoft Access has an NZ function that can achieve the same goal more simply:

SELECT NZ([TestText],"N/A") AS TestNZ FROM tblTest;

However, the NZ function is implemented in the Microsoft Access object library, not in the VBA expression service, which means that it can only be used within the Microsoft Access environment. The IIf function is implemented in the VBA expression service, and the Jet database engine “knows” how to call functions in the VBA expression service even when the query is executed outside of the Microsoft Access environment.

In addition, the NZ function is a specialised function, dedicated to the purpose of handling Null values. The IIf function is a much more flexible general-purpose function that can handle a much wider range of conditions. For example, the following query will display the letter “A” if the field “TestInteger” contains a value less than or equal to 3, otherwise it will display the letter “B” (the kind of thing you might want to do when grading test scores, for example):

SELECT IIf([TestInteger]<=3,"A","B") AS TestIIf FROM tblTest;

Interestingly enough, the query above displays the letter “B” if the “TestInteger” field contains a Null value. Under most circumstances, “Nulls propagate”, in other words, if any part of an expression is Null, the result of the expression will be Null. This is because Null represents an unknown value, and if any of the elements of an expression are unknown, then the result must also be unknown. In the example above, if the value of the field “TestInteger” is unknown, how can we determine whether the value is less than or equal to any other value? The answer is unknown, in other words, Null. The IIf function, however, can only return one of two values, the values of the “truepart” and “falsepart” arguments. It doesn’t have a “neither of the above” option! It has to evaluate the “expr” argument as either True or False, it does not have the “option” of deciding that the expression should be evaluated as Null.

Handling Null values provides us with one example of why we might use “nested” IIf functions – one or more IIf functions within another. Building on our previous example, suppose we want to distinguish between values that are less than or equal to 3, values that are greater than 3, and Null (unknown) values? The following query, using nested IIf functions, will do that job for us:

SELECT IIf([TestInteger] Is Null,"N/A",IIf([TestInteger]<=3,"A","B")) AS TestIIf FROM tblTest;

If the field “TestInteger” contains a Null value, this expression will display the text “N/A”. If the field “TestInteger” does not contain a Null value, the expression will display the result of the second, “nested” IIf function, which will be either “A” or “B”.

You can build quite complex and powerful expressions using nested IIf functions. They can, however, very quickly become difficult to read, and therefore difficult to modify when the need arises. You may find that you can achieve the same objective in a more easily maintained way using one of the other conditional functions, Choose or Switch. (I covered the Choose function in an earlier post, and I’ll cover the Switch function in a forthcoming post.)

There’s an interesting difference between the behaviour of the IIf function in VBA code and the behaviour of the same function in Jet queries. In VBA code, while the IIf function will return only one of the “truepart” or “falsepart” arguments, it will attempt to evaluate both of them. This can have “interesting” side effects. For example, suppose you attempt, in VBA code, to use the IIf function to avoid a potential divide-by-zero error:

Public Function TestIIf(TestInteger As Integer, TestLong As Long)

    TestIIf = IIf(TestInteger = 0, "N/A", TestLong / TestInteger)
    
End Function

This won’t work. If you call this function with a value of 0 in the TestInteger argument, you’ll still get a divide-by-zero error. This is because, even though the function is not going to return the result of the “falsepart” argument (TestLong / TestInteger) it still attempts to evaluate that expression, thereby raising the very error you were trying to avoid. (You can avoid this in VBA code by using If … Then … Else … End If instead of IIf).

In a Jet query, the “falsepart” argument is not evaluated if the “expr” argument (the condition to be tested) evaluates to True. The IIf function returns the value of the “truepart” argument and does not attempt to evaluate the “falsepart” argument in those circumstances. So the same expression can be used successfully to avoid the divide-by-zero error in a query:

SELECT IIf([TestInteger]=0,"N/A",[TestLong]/[TestInteger]) AS TestIIf FROM tblTest;

This works, successfully avoiding divide-by-zero errors, both within the Microsoft Access environment and when executed by our C# “JetTest” utility (see previous posts).


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.


Comments:
Free Casinos tyuueooru
http://stonewalljacksoncarnival.org/ - Play Casino
No matter how far your casino is located With online casino, you don?t have to worry about the location of your casino because the only thing that creates between you and your casino experience is the internet, so all you need to do is getting connected to the Internet and you can enjoy the gambling without having to spend your time visiting the local casinos out there.
[url=http://stonewalljacksoncarnival.org/]Free Casino Gambling[/url]
More casino options The number of online casino is far higher than bricks and mortars based casino all across the world.
Download Online Casino
Play wherever and whenever you want The best thing about online casino is that you don?t have to visit your local casino in order to meet your gambling desire.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?