In Access desktop databases you can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control on a form or report.
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. Syntax DLookup( expr, domain [, criteria] ) The DLookup function syntax has these arguments:
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. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value: Dim varX As VariantvarX = DLookup("[LastName]", "Employees", _ "[EmployeeID] = 1") Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly. You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query. You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression: =DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID) Tips
Note:Unsaved changes to records in domain aren't included when you use this function. If you want the DLookup function to be based on the changed values, you must first save the changes by clicking Save Record under Records on the Data tab, moving the focus to another record, or by using the Update method. Example
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. 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. 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 Variant intSearch = 1 varX = DLookup("[CompanyName]", "Shippers", _ "[ShipperID] = " & intSearch) Editing screen is also known as Which of the following is not present in all kinds of robot? a. program b. artificial intelligencec. computer d. body give 5 examples of UEFI cons 11.Which of the following programs is used by web clients to view the web pages? a. Web browser c. Web server b. Protocol d. Search Engine Directions: Circle the computer hardware and Box the computer software. |