Others

How to Use VBA Quotes in Access DLookup Criteria Strings

John WilsonJohn Wilson Nov 3, 2026 869 views

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.

How to Use VBA Quotes in Access DLookup Criteria Strings
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.
Before you start

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.

Solution 1Recommended

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.

1
For Numeric Fields

Use `"SerialNum=" & SerialNumber`. Numeric variables do not need to be surrounded by SQL quotes, so no extra escaping is required.

2
For Text Fields

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.

3
Handling Null Values

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.

Format Quotes Based on Data Types
Use Single Quotes as an Alternative: For text strings, you can use single quotes like `"SerialNum='" & SerialNumber & "'"` to make the code easier to read. However, this will fail if the `SerialNumber` itself contains an apostrophe.
Free Microsoft Office alternative

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. 1. Download the Installer: Visit the official WPS website and download the free WPS Office installer.
  2. 2. Install the Software: Follow the simple on-screen instructions to set up WPS Office on your computer.
  3. 3. Open Your Files: Seamlessly open your existing docx, xlsx, and pptx files and continue working without losing any formatting.
Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Free and lightweight suite for everyday office productivity.Familiar user interface with zero learning curve for Office users.Built-in PDF reader and editor for comprehensive document management.
microsoft office alternative - wps office

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.