Others

How to Fix DLookup Formula Returning No Value in MS Access

Amos Gikunda Sep 27, 2026 869 views

Question details

User needs to resolve an issue where a DLookup function fails to retrieve values based on a form selection.

How to Fix a DLookup Formula That Returns No Value in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Retrieving data (TotalPoints) from a table (tblAuditTypes) upon selecting a specific audit type in a form (frmAuditLog).
Observed behavior: The DLookup formula returns a blank or no value instead of the expected target data.

Before you start

Ensure that your table names, field names, and form control names exactly match those used in your database to avoid generic '#Name?' or null errors.

Solution 1Recommended

Correct the DLookup Criteria Syntax

Often, unwanted spaces or incorrect quotation marks in the criteria string cause DLookup to fail. Correcting the syntax resolves the issue.

When referencing text strings in a DLookup criteria argument, the value must be wrapped in single quotes. Extra spaces inside these quotes will cause the criteria to look for a mismatched string, resulting in no returned value.

Open form in Design View

Right-click your form in the navigation pane and select 'Design View'.

Locate the formula

Click on the text box where the DLookup is located, and open the Property Sheet to view its Control Source.

Update the syntax

Remove unwanted spaces around the single quotes. Use this exact syntax: =DLookUp("[TotalPoints]", "tblAuditTypes", "[AuditTypes] = '" & [Forms]![frmAuditLog]![AuditTypeCombo] & "'").

Save and test

Save your form design, switch back to 'Form View', and test the selection to ensure the value populates correctly.

Syntax check: If you are querying a numeric field rather than a text field, completely remove the single quotes from the criteria string.

Solution 2

Alternative: Retrieve Values Using a Combo Box Column

A more reliable and efficient design approach is to include the target data directly in the combo box's row source, avoiding domain aggregate functions like DLookup entirely.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Daily Office Tasks?

While WPS Office does not include a relational database tool like MS Access, it serves as a powerful, free, and lightweight alternative for your core document, spreadsheet, and presentation needs. Experience seamless compatibility with Microsoft Word, Excel, and PowerPoint files.

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 setup file and follow the on-screen instructions to quickly install the suite.
3. Open your files: Launch WPS Office and instantly open your existing Excel, Word, or PowerPoint files without compatibility issues.

Highly compatible with Microsoft Office formats (DOCX, XLSX, PPTX)Free and lightweight suite for document, spreadsheet, and presentation managementFamiliar tabbed interface requiring zero learning curveBuilt-in PDF editing tools for a comprehensive workflow

Frequently Asked Questions

Why does my DLookup formula return #Name? or #Error?

This usually happens if the field names or table names in your DLookup arguments are misspelled, or if the form controls referenced in the criteria string do not exist on the specified form.

Can I use DLookup to search for numeric values instead of text?

Yes. If your criteria field is numeric, you must omit the single quotes in the criteria string. For example: "[ID] = " & [Forms]![frmMain]![IDCombo].

Is it better to use DLookup or a Combo Box for retrieving related data?

Using a Combo Box column is generally faster and more reliable than DLookup, as it queries the data once when the form loads rather than executing a separate database query (domain function) for each lookup.

Office Quick Solutions