The Microsoft Access Ternary Operator

In a recent article, I wrote about the differences between the VBA IIf() function and the Jet/ACE engine's IIf() statement.

The IIf() Function vs. The IIf() Statement

They may look identical, but there is a very important difference in how they get evaluated.

No Longer SetMike Wolfe

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 Mid() function with Mid() statement.

2) If you open up the Expression Builder from either query design or control source, you'll find that you can locate the IIf() function under Functions -> Built-In Functions -> 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:

IIf is 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 IIf() function.

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:

1. I'm a pedant
2. 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.'"

Observe:

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.”

Infuriating.

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.

Referenced articles

Expressions vs. Code

When is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.

No Longer SetMike Wolfe

Image by TheOtherKev from Pixabay (it's an arched bridge with three prominent keystones...or are those children???)