Note: This article doesn't apply to Access web apps.You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.SyntaxDLookup( expr, domain , criteria )The DLookup function syntax has these arguments:ArgumentDescriptionexprRequired.
An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.domainRequired. A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.criteriaOptional.
A string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.RemarksThe DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module.
For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table.
Ms Access Table Dlookup
The criteria argument restricts the resulting set of records to those for which ShipperID equals 1. Dim varX As VariantvarX = DLookup('CompanyName', 'Shippers', 'ShipperID = 1')The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function.
Dlookup Vba
Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Access will obtain the current value from the control. Dim varX As VariantvarX = DLookup('CompanyName', 'Shippers', 'ShipperID = ' & Forms!Shippers!ShipperID)The next example uses a variable, intSearch, to get the value. Dim intSearch As IntegerDim varX As VariantintSearch = 1varX = DLookup('CompanyName', 'Shippers', 'ShipperID = ' & intSearch).
MS Access: DLookup FunctionThis MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. DescriptionThe Microsoft Access DLookup function returns a value from an Access table (or domain). SyntaxThe syntax for the DLookup function varies depending on what datatype you are uisng in the last parameter.
Below we show how to use the DLookup function with numeric, string, and date value in the final parameter. Numeric DLookup('FieldName', 'TableName', 'Criteria = n') String DLookup('FieldName', 'TableName', 'Criteria= 'string')TIP: Notice the single apostrophe before and after the string value.
Date DLookup('FieldName', 'TableName', 'Criteria= #date#')TIP: Notice the # symbol before and after the date value. Parameters or Arguments FieldName A field, calculation, control on a form, or function that you wish to return. TableName The set of records.
This can be a table or a query name. Criteria Optional. It is the WHERE clause to apply to the TableName. ReturnsThe DLookup function returns any datatype such as a string, numeric, date, etc. Applies ToThe DLookup function can be used in the following versions of Microsoft Access:. Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000ExampleLet's look at how to use the DLookup function in MS Access: DLookup('UnitPrice', 'Order Details', 'OrderID = 10248')In this example, you would be retrieving the UnitPrice field from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement: SELECT UnitPrice AS Expr1FROM Order DetailsWHERE (((Order Details.OrderID)=10248));You can also retrieve a calculation using the DLookup function.
For example: DLookup('UnitPrice. Quantity', 'Order Details', 'OrderID = 10248')This example would return the UnitPrice field multiplied by the Quantity field from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement: SELECT UnitPrice. Quantity AS Expr1FROM Order DetailsWHERE (((Order Details.OrderID)=10248));You could also use a form control in the DLookup function. For example: DLookup('CustomerID', 'Orders', 'OrderID = ' & Forms!Orders!OrderID)This example would return the CustomerID field from the Orders table for the record that is currently being displayed in the Orders form (based on OrderID). Example in VBA CodeThe DLookup function can be used in VBA code in Microsoft Access.For example: Dim LDate As DateLDate = DLookup('OrderDate', 'Orders', 'OrderID = 10248')In this example, the variable called LDate would now contain the OrderDate value from the Orders table where the OrderID is 10248. Example in SQL/QueriesYou can also use the DLookup function in a query in Microsoft Access.For example:In this query, we have used the DLookup function as follows: Expr1: DLookup('OrderDate','Order Details','OrderID = 10248')This query will return the OrderDate from the Order Details table where the OrderID is equal to 10248.
The results will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.For example: OrderDateValue: DLookup('OrderDate','Order Details','OrderID = 10248')The results would now be displayed in a column called OrderDateValue.