How to Use VBA Quotes in Access DLookup Criteria Strings
Question details
The user wants to understand how to correctly format quotation marks within DLookup criteria strings in Microsoft Access VBA, particularly why multiple quotes are sometimes required.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Writing VBA code that uses the DLookup function to retrieve data based on string, numeric, or Null criteria variables.
- Observed behavior
- Complex quote escaping (like four or five consecutive quotation marks) can be confusing and lead to syntax or query evaluation errors if not formatted properly.
Verify the data type of the field you are querying in your Access table, as text, numeric, and date fields require completely different quotation formatting in SQL criteria.
Format Quotes Based on Data Types
The correct DLookup syntax depends entirely on whether the variable is text, numeric, or a date. Follow these exact string concatenations to avoid syntax errors.
In VBA, quotation marks are used to define strings. When you need to pass an actual quotation mark to the SQL engine inside that string, you must type two double quotes to escape it.
Use `"SerialNum=" & SerialNumber`. Numeric variables do not need to be surrounded by SQL quotes, so no extra escaping is required.
Use `"SerialNum=""" & SerialNumber & """"`. You must wrap text values in quotes for SQL. The multiple quotation marks properly escape the quote characters within the VBA string.
Wrap your DLookup in an `Nz` function, such as `Y = Nz(DLookup("TQNum", "WarrantyT", ...), "")`. This is crucial because SQL comparisons with Null return Null rather than True or False, which can crash your VBA execution.

Use Custom Wrapper Functions
Create custom VBA functions to handle quote escaping automatically, making your code easier to read and maintain.
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access is a specialized database tool and part of the MS ecosystem, many of your daily document, spreadsheet, and presentation tasks can be handled seamlessly with WPS Office. It provides a lightweight, highly compatible, and free alternative to Microsoft Office.
- 1. Download the Installer: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the Software: Follow the simple on-screen instructions to set up WPS Office on your computer.
- 3. Open Your Files: Seamlessly open your existing docx, xlsx, and pptx files and continue working without losing any formatting.

Frequently Asked Questions
Why do I need to use so many quotation marks in Access VBA?
In VBA, literal strings are enclosed in double quotes. If you need an actual quotation mark inside that string (to satisfy the database's SQL syntax), you must type two double quotes together to escape it. This results in sequences like `""""` representing a single quote character in the final SQL evaluation.
Can I use single quotes instead of double quotes for text criteria in DLookup?
Yes, you can often use single quotes, such as `"SerialNum='" & SerialNumber & "'"`. This approach is visually simpler and widely used. However, it will cause a syntax error if the text value itself contains an apostrophe (e.g., O'Connor).
Why does my DLookup return an error when the field is empty?
If the DLookup returns no matches or queries an empty field, it evaluates to Null. If you try to assign this Null to a strict data type like a String or Integer, VBA throws an 'Invalid Use of Null' error. Always wrap DLookup functions with `Nz()`, such as `Nz(DLookup(...), 0)` to handle Null outputs safely.




