Fix Microsoft Access IIf Query Returning #Error for Lookup Fields
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.

- 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 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.
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.
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.
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.
Rewrite your IIf function to evaluate the text field from the joined table explicitly. For example: IIf(ShipType.ShipType="Airbag", QuoteShip.QuoteCost, Null).
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.

Verify Field Data Types and SQL Syntax
If you are not using lookup fields, the #Error may be caused by a direct syntax issue, null values, or an incompatible data type check within the expression itself.
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. Download and Install: Visit the official WPS Office website to download the free, lightweight suite for your Windows, Mac, or Linux device.
- 2. Export Your Database Data: Export your Microsoft Access tables or query results into a CSV or Excel format.
- 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.

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.




