Function Problems

Fix Microsoft Access IIf Query Returning #Error for Lookup Fields

Huma Ashraf ChHuma Ashraf Ch Nov 3, 2026 869 views

Question details

The user is encountering a #Error result when using an IIf function in a Microsoft Access query to calculate values based on a lookup field.

How to Fix Microsoft Access IIf Query Returning #Error for a Lookup Field
Product
Microsoft Access
Device & OS
not provided
Scenario
Calculating dynamic values, such as shipping costs, in an Access query using the IIf function to evaluate criteria from a specific field.
Observed behavior
The query returns #Error instead of the expected calculated value when evaluating the field, often due to table-level lookup fields, ambiguous names, or mismatched data types.
Before you start

Before troubleshooting, switch your Access query to SQL View and verify the exact data types of the fields involved in your IIf statement to ensure you are not evaluating a hidden numeric ID as text.

Solution 1Recommended

Replace Table Lookup Fields with Proper Query Joins

Table-level lookup fields obscure the actual stored numeric ID behind display text, causing text-based IIf evaluations to fail. Resolving this requires using standard relationships.

Using lookup fields directly in tables is widely considered a design flaw because it creates a disconnect between what is stored and what is displayed. To resolve expression errors reliably, store the foreign key as a standard Long Integer and create a join in your query.

1
Modify the Table Design

Open your primary table in Design View and change the problematic lookup field's Data Type back to a standard Number (Long Integer) or Short Text, removing the lookup properties.

2
Build a Query Join

Open your query in Design View, add both your main table and the related lookup table, and drag the primary key from the lookup table to the foreign key of the main table to establish a join.

3
Update the IIf Expression

Rewrite your IIf function to evaluate the text field from the joined table explicitly. For example: IIf(ShipType.ShipType="Airbag", QuoteShip.QuoteCost, Null).

4
Resolve Ambiguous Names

If the table name and field name are identical (e.g., ShipType table and ShipType field), assign an alias in your SQL statement (like ShipType_ShipType) to ensure Access knows exactly which field to evaluate.

Replace Table Lookup Fields with Proper Query Joins
Best Practice: Always perform lookups on forms using combo boxes rather than at the table level to prevent data type mismatch errors in queries.
Free Microsoft Office alternative

Need a Reliable Data Analysis Tool? Try WPS Office

While WPS Office does not feature a relational database application like Access, it offers a robust, free, and highly compatible alternative for Microsoft Word, Excel, and PowerPoint. If you rely on complex calculations, nested IF formulas, and robust data management, WPS Spreadsheets provides an excellent environment.

  1. 1. Download and Install: Visit the official WPS Office website to download the free, lightweight suite for your Windows, Mac, or Linux device.
  2. 2. Export Your Database Data: Export your Microsoft Access tables or query results into a CSV or Excel format.
  3. 3. Analyze Data in Spreadsheets: Open the exported data in WPS Spreadsheets and use powerful IF/IFS functions to calculate complex conditions without database query errors.
Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Advanced Spreadsheets tool for complex data analysis, filtering, and nested IF formula evaluations.Lightweight design ensuring fast installation and smooth operation on older devices.Built-in AI tools to boost productivity and help troubleshoot complex spreadsheet formulas.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query display #Error instead of the calculated value?

The #Error result typically occurs when a formula attempts to evaluate incompatible data types, encounters a division by zero, or references an ambiguous field name that exists in multiple joined tables.

Are lookup fields at the table level bad practice in Microsoft Access?

Yes, many database administrators recommend against using table-level lookup fields because they obscure the actual stored data (often an integer ID) behind display text. This creates a disconnect that frequently causes unexpected behavior and errors in queries.

How do I handle multiple calculation conditions in an Access query?

You can use nested IIf functions or the Switch function in your query's expression builder. When doing so, ensure every condition references the explicit TableName.FieldName to prevent ambiguous reference errors during execution.

How do I check the true data type of a field in an Access database?

Open the underlying table in Design View and look at the Data Type column next to your field name. If it is set to 'Number' but displays text in Datasheet View, it has been configured as a Lookup field in the Field Properties pane.