Microsoft Access IIF Function: Syntax, Examples & Guide

The Microsoft Access IIF function (Immediate IF) is a built-in conditional function that evaluates a logical expression and returns one of two values — one if the condition is TRUE, the other if FALSE. It is the Access equivalent of Excel's IF function, but in Access you use IIF throughout queries, form control sources, report expressions, and macro conditions to drive business rules without writing VBA.

Whether you are building a calculated column in a query, showing a friendly label on a form, or flagging overdue records in a report, the MS Access IIF function gives you a compact way to branch on a single yes-or-no test. Understanding its syntax, behavior, and limitations is essential for anyone who builds or maintains Access databases in a business environment.

IIf(expr, truepart, falsepart)

Each parameter has a specific role in the expression:

• expr — The condition or logical expression to evaluate (for example, [Age] > 18 or [Status] = "Active"). Access treats any non-zero, non-Null value as TRUE.
• truepart — The value returned when expr evaluates to TRUE. This can be text, a number, a date, or another expression.
• falsepart — The value returned when expr evaluates to FALSE or Null. Like truepart, it can be a literal value or a nested expression.

Note the spelling: in Access the function name is IIf with a capital I and capital F — not IF. Typing IF instead of IIf is one of the most common syntax mistakes beginners make when moving from Excel to Access.

• Query calculated fields — Add a new column in the QBE grid, turn off Show if needed, and enter an IIF expression to categorize or transform data at query time.
• Form control expressions — Set a text box Control Source to an IIF expression so labels, colors, or values change based on other fields on the form.
• Report calculated controls — Display conditional text or totals in group headers and footers without adding VBA code behind the report.
• Macro conditions — Use IIF in the Condition column of macro actions to decide whether an action runs, though VBA or data macros are often clearer for complex logic.
• Table default values — Although less common, IIF can appear in field Default Value properties when a simple conditional default is required on new records.

Example 1 — Basic pass/fail test

IIf([Score] >= 50, "Pass", "Fail")

This expression returns "Pass" when the Score field is 50 or higher; otherwise it returns "Fail". Use this pattern in a query to add a readable result column next to raw numeric scores, or on a form to give users instant feedback during data entry.

Example 2 — Nested IIF for letter grades

IIf([Score] >= 90, "A", IIf([Score] >= 75, "B", "C"))

Nested IIF lets you chain multiple thresholds. Here, scores of 90 or above receive an "A", scores from 75 to 89 receive a "B", and everything below 75 receives a "C". This works well for three-tier grading; beyond three or four branches, Switch() usually reads more clearly.

Example 3 — Null-safe phone display

IIf(IsNull([Phone]), "No phone on file", [Phone])

Empty phone fields often store Null rather than an empty string. This IIF checks for Null first and shows a friendly placeholder; otherwise it displays the actual phone number. The same pattern works for optional dates, email addresses, and notes fields in customer or inventory databases.

Example 4 — Conditional pricing flag

IIf([Quantity] >= 100, [UnitPrice] * 0.9, [UnitPrice])

A query can apply a 10% discount when order quantity reaches 100 units. This demonstrates that truepart and falsepart are not limited to text — they can be numeric expressions used directly in further calculations or report totals.

The most important gotcha with the MS Access IIF function is that Access evaluates both truepart and falsepart before deciding which result to return. Even when the condition is TRUE, falsepart is still calculated — and vice versa. This means a division-by-zero error in the unused branch will still crash the expression.

• Division by zero in the unused branch — IIf([Qty] = 0, 0, [Total] / [Qty]) still attempts [Total] / [Qty] when Qty is zero. Use IIf([Qty] = 0, 0, IIf([Qty] <> 0, [Total] / [Qty], 0)) or handle the case in VBA instead.
• Type mismatch when mixing text and numbers — truepart and falsepart must be compatible types. Returning "N/A" in one branch and 0 in the other can cause unexpected results or errors in aggregates.
• Null propagation — if expr evaluates to Null, IIF treats it as FALSE and returns falsepart. If you need three-way logic (TRUE / FALSE / UNKNOWN), combine IIF with IsNull on expr.
• Deep nesting becomes hard to read — three or more nested IIF calls are difficult to audit. Refactor to Switch() for multiple discrete outcomes, or move complex rules into a VBA function.

IIF is ideal for simple binary decisions: yes/no, active/inactive, pass/fail. It is quick to write and easy to embed in a query or form property.

Switch() evaluates a series of condition/value pairs and returns the value for the first TRUE condition — much cleaner than stacking four or five nested IIF calls. Example: Switch([Score] >= 90, "A", [Score] >= 75, "B", True, "C")

Choose() selects a value from a list based on a numeric index (1-based). It does not evaluate conditions — use it when you already have an integer slot number and want the corresponding item from a fixed list, such as mapping a month number to a month name when the number is guaranteed to be between 1 and 12.

For most conditional labeling and query logic in Access, start with IIF for two outcomes and reach for Switch() when you have three or more distinct cases. Reserve Choose() for index-based lookups where no Boolean test is needed.

Need help writing complex IIF expressions or building Access queries? Our Access developers can help — from a single calculated field to a full database redesign. Contact us below for a free quote.