Others

Fix Access IIf Query Not Returning Unmatched Records

Anum MushtaqAnum Mushtaq Nov 3, 2026 869 views

Question details

The user needs to label records as matched or unmatched using an update query with an IIf statement, but unmatched rows are being completely excluded from the result set.

Fix Access IIf Query Not Returning Unmatched Records
Product
Microsoft Access
Device & OS
not provided
Scenario
Running an update query to change a Status column by comparing records from two tables joined on a concatenated UID.
Observed behavior
The query only processes and returns rows that match in both tables, leaving unmatched records untouched or blank instead of assigning the 'Unbilled CW' status.
Before you start

Verify which table holds the complete master list of records you want to update. This primary table must be positioned on the 'left' side of your join to prevent any data from being dropped.

Solution 1Recommended

Switch to a LEFT OUTER JOIN and Modify the IIf Logic

Changing the join type ensures that all records from your primary table remain in the query results, allowing the IIf function to accurately evaluate and update unmatched rows.

An INNER JOIN inherently filters out any rows that do not exist in both tables. Because unmatched records are dropped before the update logic even triggers, your 'Unbilled CW' condition is never met. By switching to a LEFT OUTER JOIN, Access keeps all primary records and returns null values for missing related data.

1
Open Query Design View

Right-click your update query in the Access navigation pane and select 'Design View'.

2
Modify the Join Properties

Double-click the join line connecting your two tables. Select the option that reads 'Include ALL records from [YourPrimaryTable] and only those records from [YourSecondaryTable] where the joined fields are equal'.

3
Update the SET Clause

In the query grid under your Status column, change the 'Update To' criteria to evaluate for nulls. Enter exactly: IIf([YourSecondaryTable].[UID] Is Null, "Unbilled CW", "Matched").

4
Run the Update Query

Click the 'Run' button (the red exclamation mark) in the Query Design ribbon to execute the query and apply the labels.

Switch to a LEFT OUTER JOIN and Modify the IIf Logic
Best Practice: Using 'Is Null' on the right-side table's linked field is the most reliable database method to identify unmatched records in a left join.
Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Management?

If you do not require complex relational database systems like Microsoft Access for daily tasks, you can manage, filter, and reconcile your data efficiently using spreadsheets in WPS Office. It provides powerful data processing tools without expensive subscription costs.

  1. 1. Download WPS Office: Visit the official WPS website and download the free installation package tailored for your operating system.
  2. 2. Install the Application: Run the installer and follow the simple on-screen instructions to set up the software.
  3. 3. Process Your Data: Open your exported data files in WPS Spreadsheet and use built-in matching functions to identify unmatched records instantly.
Completely free and lightweight to install on PC and mobile devicesFully compatible with Microsoft Excel (.xlsx) formats for easy data migrationAdvanced VLOOKUP, XLOOKUP, and IF functions to match data without writing SQLFamiliar user interface that requires zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why does an INNER JOIN hide unmatched records in Access?

An INNER JOIN mandates that the joined fields must have matching values in both tables. If a record in the primary table does not have a corresponding UID in the joined table, the database engine completely excludes it from the result set.

How do I automatically find unmatched records without writing SQL?

You can use the built-in 'Find Unmatched Query Wizard' in Microsoft Access. Go to the Create tab, click Query Wizard, and select 'Find Unmatched Query Wizard' to automatically generate a query with the correct LEFT JOIN and 'Is Null' criteria.

Can I use multiple fields for a table join instead of concatenating them?

Yes. In the Access Query Designer, you can click and drag multiple fields individually between tables to create multiple join lines. This approach is generally more efficient and structurally reliable than merging text fields into a single UID.