Others

How to Use Multiple Criteria in Microsoft Access IIf Formula

Huma Ashraf Ch Oct 16, 2026 869 views

Question details

The user needs to calculate a conditional value in a database query based on multiple material types and numerical ranges.

How to Use Multiple Criteria in Microsoft Access IIf Formula

Product: Microsoft Access
Device & OS: not provided
Scenario: Writing a query expression to adjust a dimension value based on specific material categories, while leaving the original value unchanged for certain specified materials.
Observed behavior: The user is struggling to combine string matching (material type) and numerical conditions (height ranges) within a single functional formula.

Before you start

Verify the exact data types and spelling of your table fields in Design View, as mismatched field names or comparing string fields as numbers will cause expression syntax errors.

Solution 1Recommended

Use a Nested IIf and In() Expression in the Query

Combine nested IIf functions with the In() operator directly within the query design to evaluate string criteria before applying numerical adjustments.

This method evaluates the material type first. If the material matches a specified list, it returns the original height. If not, it falls through to subsequent nested IIf statements to add the appropriate amount based on the numerical height range.

Open Query in Design View

Open your Microsoft Access database, right-click the target query in the Navigation Pane, and select Design View.

Create a New Calculated Field

Click into a blank column in the query design grid. Type your desired alias followed by a colon (e.g., CutHeight:).

Enter the Nested Expression

Input the formula combining the material check and height check: CutHeight: IIf([MatTYPE] In ("A2 GS","A2 FGS","HRS","CRS","ALUMINUM","BRONZE","COPPER","BRASS"), [Height], IIf([Height]<=7.999, [Height]+0.015, IIf([Height]<=11.999, [Height]+0.02, [Height]+0.025))).

Run and Verify

Click the Run button (!) in the Results group on the ribbon to switch to Datasheet View and verify that the calculations are correctly applied.

Use a Nested IIf and In() Expression in the Query

Field Names: Ensure you replace [MatTYPE] and [Height] with the exact column headers used in your original database table.

Solution 2

Create a User-Defined VBA Function for Complex Logic

Use a custom VBA module with a Select Case statement to handle complex logical calculations, making your query cleaner and easier to maintain.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Management?

While Microsoft Access is built for complex relational databases, many heavy data calculation and conditional filtering tasks can be effectively managed in spreadsheets. WPS Office provides a free, lightweight suite featuring a powerful Spreadsheet application fully compatible with Excel formats. It offers robust nested IF, IFS, and SWITCH functions to handle complex data evaluation without the need for database programming.

1. Download the Suite: Visit the official WPS Office website to download and install the free software package.
2. Import Your Data: Export your current database tables to CSV or Excel formats and open them directly in WPS Spreadsheet.
3. Apply Logical Functions: Use the built-in IFS or nested IF formulas in your spreadsheet cells to instantly calculate values across thousands of rows based on multiple material criteria.

Free to use with a fast, lightweight installationFully compatible with Microsoft Excel (.xlsx and .csv) formatsFamiliar user interface requiring no steep learning curveRobust formula library including IFS and nested logical functions

microsoft office alternative - wps office

Frequently Asked Questions

How many nested IIf functions can I use in an Access query?

In Microsoft Access, you can nest up to 7 IIf functions in a single expression. If your calculation requires evaluating more than 7 distinct conditions, it is highly recommended to use the Switch function or create a custom VBA user-defined function to avoid syntax complexity.

Why does my nested IIf expression return a syntax error?

Syntax errors in nested expressions usually result from missing or misaligned parentheses, unescaped quote marks around text strings, or missing commas separating the condition, true statement, and false statement. Double-check that every opened parenthesis has a matching closing parenthesis.

Can I use the Switch function instead of nested IIfs?

Yes, the Switch function is often much easier to read than nested IIf functions when dealing with multiple mutually exclusive conditions. Switch evaluates a list of expressions sequentially and returns the corresponding value for the first expression that evaluates to true.

Office Quick Solutions