Using the Access DLookup function may be called upon where users need to output a value from other data sources (i.e. tables or queries) where there is no direct relationship.
Hang on a moment! I though Microsoft Access was a relational database system (RDBMS) so why calculate to connect to a value?
Yes it is an RDBMS but depending on how the database was designed and built or if you had inherited an Access database this could well mean having to handle workarounds and the collection of ‘D’ based Access functions are at your disposal.
The Access DLookup function amongst other ‘D’ based functions and in this short blog post I want to introduce you the structure (also known as syntax) the Access DLookup function.
Access DLookup Function: How It Works
Here’s the syntax:
DLookup ( Expr , Domain, Criteria)If you have a report or form which returns a value from another data source whether it be a table or query (which is not part of the report or form), this function will do exactly what it says; lookup that value!
There are 3 argument parameters for the Access DLookup function:
- Expr – This is an expression of the field to return its value from a table or query and can a text, numeric or any other calculation that yields the field to match.
- Domain – This is the region of data to look into. In other words, the table or query which contains the recordsets (data).
- Criteria – This is an optinal setting (but in real terms is nearly always used) which acts as the filter (or SQL statement) to filter for the first matched value to the recordset found in the domain.
For example, if you had a form which showed customers and their orders (in a sub-form), and wanted to display the manager’s name (who was not involved in the transaction) to the employee who processed the order then an example calculation is a Textbox control might look something similar to:
DLookup("[ManagerName]", "tbl_Employees", "[EmployeeName] = [txt_Employee]")The “[ManagerName]” is the field to look into the domain “tbl_Employees” where the criterion matches the first instance from the Textbox [txt_Employee] against the field [EmployeeName] (in tbl_Employees).
The Access DLookup function is one of twelve (in Access 2010) ‘D’ functions available and have the same syntax.
To learn more about this function and other Access functions, I cover this across a selection of MS Access database eBooks which may be of interest to you.
Tags: access dlookup, Access functions, calculating in ms access, ms access dlookup, ms access functions
If you use a field which is indexed (as defined in the table design), this will help with performance too as some nested functions will degrade as it needs to work hard to return a value. In fact, using a custom built VBA function may further reduce the performance and keeping code streamlined will be highly recommended.