Fix Access IIf Query Not Returning Unmatched Records
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.

- 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.
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.
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.
Right-click your update query in the Access navigation pane and select 'Design View'.
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'.
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").
Click the 'Run' button (the red exclamation mark) in the Query Design ribbon to execute the query and apply the labels.

Optimize Table Relationships Using Primary Keys
Avoid joining tables based on concatenated string fields, which can cause poor performance and unexpected update behaviors. Use properly indexed primary keys instead.
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. Download WPS Office: Visit the official WPS website and download the free installation package tailored for your operating system.
- 2. Install the Application: Run the installer and follow the simple on-screen instructions to set up the software.
- 3. Process Your Data: Open your exported data files in WPS Spreadsheet and use built-in matching functions to identify unmatched records instantly.

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.




