Others

How to Use a String Variable with Microsoft Access DLookup

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to execute a DLookup expression that has been stored as a text string inside a variable in Microsoft Access.

Product: Microsoft Access
Device & OS: not provided
Scenario: Attempting to dynamically build and run a DLookup function using string variables in VBA.
Observed behavior: Assigning a DLookup expression to a string variable only stores the text; Access does not automatically evaluate or execute the text as code.

Before you start

Ensure you have access to the VBA Editor in your Microsoft Access database (press ALT + F11) and basic familiarity with writing VBA modules.

Solution 1Recommended

Use a VBA Wrapper Function (Recommended)

Instead of storing the entire DLookup command as a string, create a custom VBA function that accepts your dynamic criteria as an argument and executes DLookup directly.

Passing a raw string expression to evaluate is often inefficient and difficult to troubleshoot. A wrapper function provides a clean, reusable way to perform lookups while properly executing the code.

Open the VBA Editor

In Microsoft Access, press ALT + F11 to launch the Visual Basic for Applications editor.

Create a New Module

Click 'Insert' from the top menu and select 'Module' to create a new code module.

Write the Wrapper Function

Define a custom function, such as: `Public Function GetWidgetID(strCriteria As String) As Variant`.

Execute DLookup Inside the Function

Within the function, call DLookup directly. Use the Nz function to handle potential nulls: `GetWidgetID = Nz(DLookup("WidgetID", "Widgets", strCriteria), 0)`.

Call the Function

Use your new `GetWidgetID("Your Criteria Here")` function directly in your queries, form events, or other VBA subroutines.

Handling Null Values Safely: Wrapping the DLookup in the Nz() function ensures that if no matching record is found, the function returns a default value (like 0 or an empty string) instead of throwing an 'Invalid Use of Null' error.

Solution 2

Evaluate the String Expression using Eval()

If you absolutely must execute a complete function call stored as a text string, you can use the Access Eval() function.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Needs?

While Microsoft Access is used for complex databases, most everyday data management, lookup tasks, and reporting can be efficiently handled using powerful spreadsheets. WPS Office provides a free, highly compatible alternative to Microsoft Office for all your document, spreadsheet, and presentation needs.

1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
2. Import Your Data: Open WPS Spreadsheet and easily import your existing data tables or export them from your database as CSV/Excel files.
3. Perform Data Lookups: Use powerful built-in spreadsheet lookup functions like VLOOKUP or XLOOKUP to easily query and relate your data without writing complex VBA code.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Advanced spreadsheet functions like VLOOKUP, XLOOKUP, and INDEX/MATCH to handle data lookups without VBA.Free, lightweight suite that consumes minimal system resources.Familiar tabbed user interface allows for seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Access execute my DLookup string automatically?

In VBA, assigning a string like `"DLookup(...)"` to a variable merely stores the literal text in memory. Access does not automatically parse and execute text strings as code unless they are explicitly evaluated using a method like `Eval()`.

How do I handle null values returned by DLookup?

It is best practice to wrap your DLookup function in the `Nz()` function. For example, `Nz(DLookup("FieldName", "TableName", "Criteria"), 0)` will return 0 instead of throwing a VBA error if no record matches your criteria.

Is it better to use Eval() or a wrapper function for DLookup?

It is highly recommended to use a wrapper function. Wrapper functions are compiled by the VBA editor, making them easier to debug, less prone to syntax errors, and significantly faster to execute than parsing text strings at runtime with `Eval()`.

Can I use variables inside the DLookup criteria string?

Yes, you can dynamically build the criteria string by concatenating VBA variables into it. For example: `strCriteria = "WidgetID = " & lngWidgetID`. Remember to wrap text criteria in single quotes and date criteria in `#` symbols.

Office Quick Solutions