DLookUp, Access Expression Builder
This article will explain the DLookUp function used in the Access expression builder.
A DLookup function is used to get the value of a particular field (for instance, a column of a table or a calculated field) from a specified set of records (for instance, a table or a query).
You can use this DLookup function in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control on a form or report. It is very similar to Vlookup in Excel.
Contents
Syntax:
=DLookup(expr, domain [, criteria] )
expr:
The field whose value will be returned. String expression. (This is equivalent to the select clause in a Select statement)
domain:
The set of records where the value will be retrieved from. String expression. (Equivalent to the from clause of select statement)
criteria:
The criteria which will be used to get the value from. String expression. (Equivalent to the where clause of select statement).
Before we have a look at the example, here are few points to note about this function:
- You should specify criteria that will ensure that the field value returned by the DLookup function is unique. If more than one field meets criteria, the DLookup function returns only the first occurrence.
- Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.
- If you do not specify any 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.
Example 1: Extracting First Name of student with a given ID
Consider the following table:
and the following form:
Using the following expression as the control source of the text box will cause it to display the first name of the record with the ID 3110:
Expression:
=DLookUp("[First Name]", "[Students]", "[Students].ID = 3110")
Result:
Example 2: Extracting First and Last Name of student with a given ID
Same as the previous example, only this time we want the First and Last Name to be written in the text box:
Expression:
=DLookUp("[First Name]","Students","[Students].ID = 3110") & " " & DLookUp("[Last Name]","Students","[Students].ID = 3110")
Result:
Example 3: Referring Form Control
This example will use the same table as the previous examples but will use the form below. This form has 2 textboxes:
The user inputs the ID of a student in the first text box. We want an expression that will print the first name of the student associated with the ID in the first text box in the second text box. In this example the first text box has the name txtName:
The following expression should be used as the Control Source for the second text box:
Expression:
=DLookUp("[First Name]","[Students]","[Students].ID =" & Str([txtName].[Text]))
The [txtName].[Text][txtName]& Str([txtName].[Text])
Result:
Note: You may need to refresh the form for the value in the second text box to update.
Example 4: Using DLookup function in an SQL statement
Consider that you have a Products table as below:
Say, you want to list all the products that have a price greater than price of Product “SKU001”. We will divide this into two parts:
Part 1: To get the price of product “SKU001”, you can use the DLookup function as below:
DLookUp("[Price]","[Products]","[Products].SKU = 'SKU001'")
Part 2: And to get a list of products with price greater than this, you can use this condition in an SQL statement as below:
SELECT SKU, Desc, Price
FROM Products
WHERE Price > DLookUp(“[Price]”,”[Products]”,”[Products].SKU = ‘SKU001′”);
The result of the query will be as below:
You can download the sample file used in this article from the link below:
See also:
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “DLookUp, Access Expression Builder”