Others

How to Fix Access DLookup Error 2471 with Text Criteria

Steven ParkerSteven Parker Nov 3, 2026 870 views

Question details

The user is encountering runtime error 2471 when running a DLookup or DCount expression because text values are not properly quoted in the criteria string.

How to Fix Access DLookup Error 2471 with Text Criteria
Product
Microsoft Access
Device & OS
not provided
Scenario
Checking for duplicate records or looking up values using DLookup or DCount in VBA with text criteria from a TempVars variable.
Observed behavior
The database returns runtime error 2471 due to missing quotation marks around text values, and fails if the text itself contains apostrophes.
Before you start

Ensure you have access to the VBA editor in your Microsoft Access database and identify the exact line of code triggering the DLookup or DCount error.

Solution 1Recommended

Properly Format Text Criteria and Escape Apostrophes

Fix the syntax error by enclosing the text criteria in single quotation marks and handling potential apostrophes within the data.

When passing a text variable into an Access domain aggregate function, it must be enclosed in single quotation marks. Furthermore, if the text itself contains an apostrophe (like O'Connor), it breaks the string format. You must escape it using the Replace function to prevent runtime errors.

1
Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the VBA environment and locate your DLookup or DCount expression.

2
Enclose Text in Single Quotes

Modify the criteria string to wrap the text variable in single quotes. For example, change it to: "FName ='" & TempVars!FirstName & "'".

3
Escape Internal Apostrophes

To prevent errors from apostrophes inside the names, wrap your variable in a Replace function: "FName='" & Replace(TempVars!FirstName, "'", "''") & "'".

Properly Format Text Criteria and Escape Apostrophes
Optimization Tip: Using DCount instead of DLookup is generally faster and more efficient if you only need to verify whether a matching record exists.
Free Microsoft Office alternative

Boost Your Productivity with WPS Office

While Microsoft Access handles complex databases, most everyday data management, filtering, and reporting tasks can be efficiently managed using robust spreadsheet tools. WPS Office provides a highly compatible, free alternative to Microsoft Office, featuring powerful Writer, Spreadsheet, and Presentation applications.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
  2. 2. Install the Software: Run the downloaded setup file and follow the simple on-screen instructions to complete the installation.
  3. 3. Open Your Documents: Launch WPS Office and instantly open, edit, and save your existing Microsoft Office files without losing any formatting.
Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation with a clean, familiar, and easy-to-use interface.Advanced spreadsheet features for data filtering, pivot tables, and VBA-like macros for data tasks.Built-in PDF editor for seamless document conversion and editing.
QA img-9

Frequently Asked Questions

Why does DLookup return Error 2471?

Error 2471 typically occurs when the criteria parameter in a domain aggregate function like DLookup is improperly formatted. For text fields, the variable must be explicitly wrapped in single quotation marks within the criteria string.

How do I handle apostrophes in DLookup criteria?

Use the Replace function to double the apostrophes in your text string. For example: Replace(YourVariable, "'", "''"). This ensures Microsoft Access reads the apostrophe as part of the text rather than the end of the SQL string.

Should I use DLookup or DCount to check for duplicates?

DCount is usually the better choice for checking duplicates. DLookup returns the actual value (or Null if not found), which requires handling IsNull checks, whereas DCount simply returns the number of matching records (0 if none exist).