Others

How to Fix Access DLookup Runtime Error 94 and Error 2471

Maira Mehtab Sep 20, 2026 869 views

Question details

The user is attempting to retrieve values such as analyzer types, lead times, or price factors using a DLookup expression based on a combo box selection, but encounters runtime errors.

Product: Microsoft Access
Device & OS: not provided
Scenario: Using a DLookup function to look up database records based on the value selected in a multi-column combo box.
Observed behavior: The DLookup expression fails, returning Runtime Error 94 (Invalid use of Null) or Error 2471, typically caused by comparing a text string with a numeric ID field.

Before you start

Verify the data type of the target field in your database table to determine if you should be comparing a numeric ID or a text string in your DLookup expression.

Solution 1Recommended

Retrieve Values Directly Using the Combo Box Column Property

The most efficient way to get related data from a combo box is to bypass the DLookup function entirely and reference the combo box's built-in columns.

Since the combo box already loads the required data (analyzer types, lead times, and price factors) into its rows, querying the database again with DLookup is unnecessary and prone to errors. You can extract the value straight from the control.

Open the form in Design View

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

Identify the combo box name

Click on your combo box and open the Property Sheet to find its exact Name (e.g., Combo11).

Reference the Column property

In your VBA code or control source, use the Column property to retrieve the data. Note that column indexes are zero-based, so use Combo11.Column(2) to get the value from the third column.

Zero-Based Indexing: Remember that Column(0) refers to the first column, Column(1) refers to the second, and Column(2) refers to the third column.

Solution 2

Format Text Criteria with Single Quotes in DLookup

If your combo box returns a text value but your criteria lacks proper string formatting, adding single quotes will resolve the Error 2471 evaluation failure.

Solution 3

Use a Hidden Numeric ID Column for Comparison

If your target table utilizes a numeric ID as the primary key, update your combo box to bind to this numeric ID to ensure data types match perfectly during the DLookup.

Free Microsoft Office alternative

Looking for a Lightweight and Free Office Alternative?

While WPS Office does not include a direct database management equivalent to Microsoft Access, it is an exceptional, free alternative for your everyday document processing, spreadsheet management, and presentation needs. Avoid expensive subscriptions while maintaining full format compatibility.

1. Visit the official website: Go to the WPS Office website to download the latest free version.
2. Install the software: Run the installer and follow the quick on-screen instructions to set up the suite.
3. Open your files: Launch WPS Office to easily create, edit, and save your documents, spreadsheets, and presentations.

Free, comprehensive office suite for daily productivityFully compatible with Microsoft Word, Excel, and PowerPoint formatsFamiliar and intuitive tabbed user interfaceLightweight installation with built-in PDF editing tools

microsoft office alternative - wps office

Frequently Asked Questions

What causes DLookup Runtime Error 94 (Invalid Use of Null)?

Runtime Error 94 occurs when your DLookup expression finds no matching records and returns a Null value, but the variable or control receiving the data cannot accept Nulls. You can prevent this by wrapping the DLookup in the NZ() function to provide a default value, such as NZ(DLookup(...), 0).

Why am I getting Error 2471 when running a DLookup expression?

Error 2471 typically means Microsoft Access evaluated your expression but could not locate the specified field. This usually happens if a field name is misspelled, or if you are comparing a text string without wrapping it in single quotes, which causes Access to mistakenly interpret the string data as a field name.

How do I extract a specific value from a multi-column combo box in Access?

You can extract data directly using the Column property instead of querying the database again. Simply use the syntax Me.ComboBoxName.Column(index). Keep in mind that the index is zero-based, meaning Column(0) represents the first column and Column(1) represents the second.

Office Quick Solutions