How to Use Microsoft Access DLookup with State and Mileage Criteria
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.

- 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.
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.
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.
In your VBA editor, start by declaring a string variable for your criteria: Dim strCriteria As String
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)"
Add Debug.Print strCriteria right below your string construction to output the resolved criteria to the Immediate Window for easy troubleshooting.
Use the compiled string in your DLookup statement to fetch the rate: CartageRate = DLookup("Rate", "NASRatesbyMiles", strCriteria)

Use the BETWEEN Operator for Inclusive Boundaries
If your lower and upper mileage limits are inclusive, you can simplify the SQL logic by using the BETWEEN operator.
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. Download WPS Office: Visit the official WPS website to download the free, lightweight installer.
- 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.

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.




