> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# The Microsoft Access Ternary Operator
- URL: https://nolongerset.com/ternary-operator-iif/
- Published: 2022-01-07T03:10:53.000Z
- Updated: 2026-05-08T13:00:23.000Z
- Description: 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?
- Author: Mike Wolfe
- Tags: Advanced, Commentary, #Import 2026-05-20 02:59

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() StatementThey may look identical, but there is a very important difference in how they get evaluated.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/01/work-of-art-1798585_1920.jpg)](https://nolongerset.com/iif-function-vs-statement/)

In the [comments](https://nolongerset.com/iif-function-vs-statement/#commento-comment-name-ce661063915a5714f681f0738bdce6706732f240d78ca9cdb1933dafd9535380) 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](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-function) with [Mid() statement](https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/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:

```sql
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](https://en.wikipedia.org/wiki/Ternary%5Foperation#Computer%5Fscience) for a moment:

> In computer science, a ternary operator is an [operator](https://en.wikipedia.org/wiki/Operator%5F%28computer%5Fprogramming%29) 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](https://en.wikipedia.org/wiki/%3F:):

> 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](https://en.wikipedia.org/wiki/Visual%5FBasic%5F.NET) 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](https://nolongerset.com/expressions-vs-code/)) 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](https://www.nationalreview.com/2012/05/big-ing-joker-jonah-goldberg/):

> 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. CodeWhen is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/10/goats-173940_1280.jpg)](https://nolongerset.com/expressions-vs-code/)

*Image by [TheOtherKev](https://pixabay.com/users/theotherkev-9436196/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4311095) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4311095) (it's an arched bridge with three prominent keystones...or are those children???)*