databasedev.co.uk - database solutions and downloads for microsoft access

DLookup Domain Function

DLookup Function in Microsoft Access:

Microsoft Access is a powerful data-handling package. It has many strong features that make data retrieval simple. One of them is the DLookup function.

DLookup is a function used to retrieve a value of a particular field from a domain. A domain can be a specified set of records from a table, query or SQL expression. You can use the DLookup function to get information that is not a part of the underlying record set. For example you have a mark sheet form based on a record set having RollNo, Marks1, Marks2, Marks3. You can use DLookup to get the Student name from another table.

DLookup Function Syntax:

DLookup (fieldname, domain[, condition])

<fieldname> It identifies the field whose value you want to return. It may be a table or query field, or an expression based on that field. You can include the name of a field in a table, a control on a form, a constant or a function (built-in or user-defined, but not another domain aggregate or SQL aggregate function)
<domain> This is the name of the record set that specifies the range of records that constitutes the domain. It can be a table name or a query name.
<condition>

This is optional. It is a string expression for restricting the range of data on which the DLookup function is performed. It is often similar to the WHERE clause in an SQL expression, minus the word WHERE.

If condition is not used, the DLookup function evaluates <fieldname> against the entire domain.

The DLookup() function returns one value from a single field. If there are no record that satisfies the criteria, or if the domain is empty, DLookup() returns a Null. If more than one record satisfies the criteria, DLookup returns the first instance of that <fieldname>. The criteria should be judiciously defined so that the DLookup function returns a unique field. If the criteria fields are text, enclose the text in single quotation marks.

For evaluating date or time values, enclose the date or time value in number signs (#).

DLookup Function Examples

Dim newvar as variant
Newvar = DLookup("[student name]","studentmaster","[rollno]=1001")

This example extracts the data from the field [student name] from the table studentmaster for the roll number 1001.

Let us see what happens if the DLookup function were to be given without the criteria.

Newvar = DLookup("[student name]","studentmaster")

This will return the data in the field [student name] from the first record of the table studentmaster.

You can use variables in the criteria too. For instance, a form field can be used as <condition>

Newvar = DLookup("[student name]","studentmaster","[rollno]=Form![rollno]")

This will compare the data in [student name] field from the table studentmaster with that in the [rollno] field of the current form.

To specify textual criteria that comes from a field on a form include the single quotes.

Nvar = DLookup("[rollno]","studentmaster",
"[student name]=’ " & Forms![Marks]![student name] & " ' ")

The DLookup function can be used as an expression in a Microsoft Access query or a calculated field in a form to retrieve foreign values. You can also use it in the Criteria field of the query builder. However, it is more efficient to create a new query containing fields of both the tables and use it as a base record set.

DLookup only selects the saved records of the domain. Any unsaved record is not included in the domain.