DLookUp, Access Expression Builder

This article will explain the DLookUp function used in the Access expression builder.


Syntax:

=DLookup(expr, domain [, criteria] )

expr:

The field whose value will be returned. String expression.

domain:

The set of records where the value will be retrieved from. String expression.

criteria:

The criteria which will be used to get the value from. String expression.


Example 1:

Consider the following table:
Table
and the following form:
Form 1

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 1

Expression:

=DLookUp("[First Name]", "[Students]", "[Students].ID = 3110")

Result:
Result 1


Example 2:

Same as the previous example, only this time we want the First and Last Name to be written in the text box:
Expression 2

Expression:

=DLookUp("[First Name]","Students","[Students].ID = 3110") & " " & DLookUp("[Last Name]","Students","[Students].ID = 3110")

Result:
Result 2


Example 3:

This example will use the same table as the previous examples but will use the form below. This form has 2 textboxes:
Form 2
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:
txtName

The following expression should be used as the Control Source for the second text box:
Expression 3

Expression:

=DLookUp("[First Name]","[Students]","[Students].ID =" & Str([txtName].[Text]))

The [txtName].[Text] expression returns the value in the textbox [txtName]. The expression & Str([txtName].[Text]) converts it to a string and concatenates it to the rest of the criterion expression.

Result:
Result 3
Note: You may need to refresh the form for the value in the second text box to update.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
Additional Info