The Microsoft Access Ternary Operator
It's a function. It's a statement. It's an operator. It's a--what the heck do we call this short-circuit-evaluating IIf() thing and why does it even matter?
In a recent article, I wrote about the differences between the VBA IIf() function and the Jet/ACE engine's IIf() statement.
In the comments section of that article, Ben Clothier took issue with my use of the term "statement" to describe the Jet/ACE database engine's IIf() implementation:
Forgive me for wading into semantics, but while I agree with everything you've described, I hesitate to describe this as a statement vs function:
1) A statement implies that it executes an instruction while a function returns a result of an evaluation. Under that definition, all examples in the articles are examples of a function, rather than a statement. Furthermore, it's also a VBA construct. For example, contrast the
2) If you open up the Expression Builder from either query design or control source, you'll find that you can locate the
Program Flow. Nowhere within the expression builder the term "statement" is used.
My response to that is...I agree.
I never really liked the term "statement" to describe the non-VBA IIf(). But I needed to call it something besides the word "function."
The Non-VBA IIf() is NOT a Function
When I think of a function, I think of a language construct that receives arguments and returns a value. One of the key attributes of a function, though, is that each of those arguments is evaluated by the language before being passed to the function. That clearly is not happening with the non-VBA IIf() ... thing ... because otherwise SQL queries like this would result in errors:
SELECT IIf(Total = 0, 0, Successes / Total) AS SuccessAvg FROM MyAggregateQuery
The non-VBA IIf() avoids division by zero errors in statements like the one above through the use of short-circuit evaluation. Whereas the IIf() function has to evaluate both the truepart (
0) and the falsepart (
Successes / Total) regardless of the value of the condition (
Total = 0), the non-VBA IIf() checks the condition first and then evaluates EITHER the truepart OR the falsepart, but never both.
If only there were a programming term for such a language construct...
What is a Ternary Operator?
Since this is 2021, let's just turn things over to Wikipedia for a moment:
In computer science, a ternary operator is an operator that takes three arguments (or operands).
By far, the most common ternary operator is the conditional operator, read aloud as "if a then b otherwise c."
More from a different Wikipedia article:
Although many ternary operators are possible, the conditional operator is so common, and other ternary operators so rare, that the conditional operator is commonly referred to as the ternary operator.
If we continue reading the Wikipedia article on the ternary operator, we eventually get down to the section on Visual Basic. Though it refers to Visual Basic.NET, its commentary on the IIf() function applies equally to VBA:
IIfis a ternary function, but not a ternary operator. As a function, the values of all three portions are evaluated before the function call occurs.
The article continues:
Visual Basic Version 9 has added the operator
If()in addition to the existing
IIf()function that existed previously. As a true operator, it does not have the side effects and potential inefficiencies of the
Now, again, the paragraph above refers to Visual Basic.NET and not VBA. However, the Jet/ACE database engine implementation of IIf() exhibits the same behavior as VB.NET's If() operator.
And, so, I shall stop referring to the Jet/ACE IIf() as a statement and I will henceforth refer to it as an operator.
Why Does This Matter?
A normal person might fairly ask why I've now devoted three articles (here's the first) to the Jet/ACE IIf() operator, with a large percentage of those articles centered–directly or indirectly–around the topic of what to actually call this thing.
Upon reflection there are two primary reasons:
- I'm a pedant
- Words matter
As programmers, we work in a very technical field. The ability to communicate technical knowledge depends in large part on having a shared vocabulary where certain words have certain meanings. This particular case makes that case perfectly.
How are we supposed to be able to distinguish between two very different concepts–an IIf() function vs. an IIf() operator–if we use the same word, "function," to describe both things?
And yet, as Ben rightly points out, that's exactly what Microsoft themselves have done.
However, there's another confounding factor. If you look at the expression builder, they have a menu entry for
Operators, and the
IIf()is nowhere within the list of all operators. For that reason, unwary users will expect that the
IIf()is a function even though it's definitely nothing like
VBA.IIf()function. I assume they did that just because the
IIf()looks likes a function more than it does an operator so they just classified it as a function for user's convenience. As long you address that caveat, I'm fine with calling it an operator.
I'll admit, this fact bothers me more than it should. It's not simply that Microsoft chose to include IIf() in the "Functions" section of the expression builder. It's that they have an "Operators" section that is a better fit semantically for the Jet/ACE database engine IIf(), but they chose not to use it.
It's like when Joe Biden misuses the word "literally" and you think to yourself, "ah, well, lots of people misuse literally these days as a hackneyed synonym for 'really' and not as an antonym for 'figuratively.' He deserves a pass on this one." But then he goes on to literally say, "I'm using this word as an antonym for 'figuratively.'"
He says JFK’s “call to service literally, not figuratively, still resounds from generation to generation.” He told students in Africa, “You are the keystone to East Africa — literally, not figuratively, you are the keystone.” “The American people are looking for us as Democrats,” he has said. “They’re looking for someone literally, not figuratively, to restore America’s place in the world.”
Now, I'm sure Ben is right in his reasoning that "they did that just because
IIf() looks like a function more than it does an operator." And, honestly, if I were designing that Expression Builder form, I would have classified IIf() the same way. I suppose the original sin, then, was making the IIf() operator look like a function in the first place. The Jet/ACE database engine should have used symbols like they did for all the other operators, like
Mod (wait, d'oh!).
OK, I'm rambling now.
I've been shouting about this on my street corner for so long now that even the tourists have stopped staring and have decided to move on with their day. And, so, I bid you adieu. Please enjoy the rest of your stay in this fine city. And, if you are looking for something to do later, consider taking the
IIf() operator out for a short-circuit-evaluating spin on the town. You won't regret it.
Image by TheOtherKev from Pixabay (it's an arched bridge with three prominent keystones...or are those children???)