Others

How to Fix Access DLookup Syntax Errors with Special Characters

Bushra Parveen Sep 28, 2026 868 views

Question details

The user needs to resolve syntax errors that occur when using the DLookup function in Microsoft Access with criteria containing special characters like apostrophes.

How to Fix Access DLookup Syntax Errors with Special Characters

Product: Microsoft Access
Device & OS: not provided
Scenario: Building DLookup expressions with string criteria containing apostrophes in fields such as Project or BOM numbers.
Observed behavior: The DLookup expression produces a syntax error because the unescaped apostrophe prematurely terminates the criteria string.

Before you start

Identify the exact fields in your DLookup criteria that might contain apostrophes or quotes (such as Project IDs or BOM numbers) and open your VBA editor to the relevant module.

Solution 1Recommended

Use the Replace Function to Escape Apostrophes

Prevent syntax errors by replacing single quotes with two single quotes within the DLookup criteria string.

In Microsoft Access, text strings in criteria are usually enclosed in single quotes. If the text itself contains a single quote (an apostrophe), it breaks the string format. You can resolve this by using the Replace function to double up the single quote.

Locate the Expression

Open the VBA editor in Microsoft Access and navigate to the module or form containing your DLookup expression.

Apply the Replace Function

Wrap the variable or field that contains the apostrophe (e.g., [BOM#]) with the Replace function to change one single quote to two single quotes: Replace([BOM#], "'", "''").

Construct the Full String

Concatenate the sanitized value back into your expression. For example: If Not IsNull(DLookup("[1EngDetailer]", "[Orders]", "[Project]= '" & [Project] & "' AND [CustomizedItem]= '" & Replace([BOM#], "'", "''") & "'")) Then

Use the Replace Function to Escape Apostrophes

Best Practice: Always validate and sanitize all user-entered criteria values using the Replace function before building Access expressions to ensure robust database queries.

Solution 2

Delimit String Criteria with Double Quotes

Use double quotes instead of single quotes to enclose criteria values if the data contains apostrophes but no double quotes.

Free Microsoft Office alternative

Looking for a Free, Lightweight Data Management Alternative?

While Microsoft Access handles complex relational databases, you can easily manage, analyze, filter, and track substantial amounts of data using WPS Spreadsheets. WPS Office is a free, lightweight, and comprehensive suite that lets you handle your daily data tasks smoothly without expensive database software subscriptions.

1. Download the Installer: Visit the official WPS Office website and click the free download button to obtain the setup package.
2. Install WPS Office: Run the downloaded file and follow the quick on-screen prompts to complete the lightweight installation process.
3. Manage Your Data: Launch WPS Spreadsheets to easily open your existing data files and start organizing records using built-in analytical tools.

Highly compatible with Microsoft Office formats, including Excel (.xlsx, .csv).Lightweight software that runs quickly and smoothly on almost any device.Familiar, easy-to-use interface requiring no steep learning curve.Advanced data filtering, pivoting, and validation functions for efficient data tracking.

Frequently Asked Questions

Why does an apostrophe cause a syntax error in Access DLookup?

In Microsoft Access, single quotes (apostrophes) are typically used to mark the beginning and end of text values within SQL criteria strings. When a field contains an internal apostrophe, Access interprets it as the end of the text string, which causes a syntax error when it tries to parse the remaining characters.

Can I use double quotes instead of the Replace function?

Yes, you can enclose your criteria string in double quotes instead of single quotes (e.g., using "" in VBA). However, this method will fail if your data entries happen to contain both single and double quotes.

How do I sanitize user input for Access criteria?

You can sanitize user input by passing all text criteria through a custom VBA function that applies the Replace function to escape single quotes, ensuring malicious or accidental special characters do not break your SQL statements.

Does this escaping method apply to other Access functions?

Yes, escaping apostrophes by replacing them with two single quotes applies to other domain aggregate functions like DCount, DMax, DMin, and when building standard SQL strings for queries in VBA.

Office Quick Solutions