How to Fix Access DLookup Error 2471 with Text Criteria
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.

- 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.
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.
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.
Press ALT + F11 in Microsoft Access to open the VBA environment and locate your DLookup or DCount expression.
Modify the criteria string to wrap the text variable in single quotes. For example, change it to: "FName ='" & TempVars!FirstName & "'".
To prevent errors from apostrophes inside the names, wrap your variable in a Replace function: "FName='" & Replace(TempVars!FirstName, "'", "''") & "'".

Use DCount to Verify Record Existence
Switch from DLookup to DCount when your only goal is checking for duplicate entries, as it handles null checks more elegantly.
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. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the Software: Run the downloaded setup file and follow the simple on-screen instructions to complete the installation.
- 3. Open Your Documents: Launch WPS Office and instantly open, edit, and save your existing Microsoft Office files without losing any formatting.

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).




