Others

How to Use Microsoft Access DLookup with State and Mileage Criteria

Bushra ParveenBushra Parveen Nov 3, 2026 870 views

Question details

The user needs to construct a Microsoft Access DLookup expression using VBA that retrieves a rate based on a specific destination state and a mileage value falling within a lower and upper limit.

How to Use Microsoft Access DLookup with State and Mileage Criteria
Product
Microsoft Access
Device & OS
not provided
Scenario
Building a VBA expression to retrieve a database value (CartageRate) using both text (State) and numeric range (Mileage) criteria dynamically from form controls.
Observed behavior
The goal is to accurately query the NASRatesbyMiles table by dynamically injecting form control values into the DLookup criteria string, while handling both text syntax and numerical boundary comparisons.
Before you start

Ensure your Microsoft Access form has the correct control names (like cboDestState and DestMileage) and that your target table contains the matching fields (Rate, DestinationState, LowerMileage, UpperMileage) before writing your VBA code.

Solution 1Recommended

Build the DLookup Criteria String Using VBA

Use a VBA string variable to properly format and concatenate your form values with the database table fields, ensuring correct syntax for text and numeric ranges.

When using DLookup with multiple criteria, especially mixing text and numeric values, building the criteria as a separate string variable is the safest and cleanest method. Text fields require single quotes, whereas numeric fields do not.

1
Declare the criteria string variable

In your VBA editor, start by declaring a string variable for your criteria: Dim strCriteria As String

2
Construct the criteria string with concatenated values

Assign the criteria logic to the variable, making sure to wrap the text field (DestinationState) in single quotes and use logical ANDs for the mileage boundaries: strCriteria = "DestinationState='" & Me!cboDestState & "' And (" & Me!DestMileage & " >= LowerMileage And " & Me!DestMileage & " <= UpperMileage)"

3
Verify the string with Debug.Print

Add Debug.Print strCriteria right below your string construction to output the resolved criteria to the Immediate Window for easy troubleshooting.

4
Execute the DLookup function

Use the compiled string in your DLookup statement to fetch the rate: CartageRate = DLookup("Rate", "NASRatesbyMiles", strCriteria)

Build the DLookup Criteria String Using VBA
Tip: Using Debug.Print is a highly effective way to catch syntax errors like missing spaces or quotes before the DLookup executes.
Free Microsoft Office alternative

Looking for a Lightweight and Free Microsoft Office Alternative?

While WPS Office does not include a database application like Microsoft Access, it offers a robust, free, and highly compatible alternative for Microsoft Word, Excel, and PowerPoint. If you are managing complex datasets, WPS Spreadsheet provides advanced data processing tools and lookup formulas (like VLOOKUP, XLOOKUP, and INDEX/MATCH) that can often replace complex database queries for everyday data management.

  1. 1. Download WPS Office: Visit the official WPS website to download the free, lightweight installer.
  2. 2. Manage Data with WPS Spreadsheet: Open WPS Spreadsheet to organize your tables, apply advanced lookup formulas, and manage tabular data as an alternative to complex databases.
Fully compatible with Microsoft Office formats (.docx, .xlsx, .pptx).Powerful Spreadsheet application with advanced lookup formulas (XLOOKUP, VLOOKUP) to manage data without needing Access.Lightweight, fast installation, and completely free to use for core daily office tasks.Familiar tabbed interface allows seamless migration from Microsoft Office.
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a syntax error in my VBA DLookup criteria?

Syntax errors typically occur when text values are not wrapped in single quotes, date values are missing the '#' delimiter, or there are missing spaces around your '&' concatenation operators. Using 'Debug.Print' helps reveal the exact string Access is trying to process.

How do I view the output of Debug.Print in Access?

You can view the output in the VBA Immediate Window. Press Ctrl + G while in the VBA editor to open the Immediate Window and inspect your generated criteria string.

Can I use formulas like VLOOKUP in a spreadsheet instead of DLookup in Access?

Yes, if your data is exported to a spreadsheet application like WPS Spreadsheet or Excel, you can use formulas such as VLOOKUP, XLOOKUP, or an INDEX and MATCH combination to find records based on multiple criteria without having to write VBA code.