DFirst, DLast Functions

You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro, module, query expression, or calculated control on a form or report.

Syntax

DFirst ( expr , domain [, criteria] )

DLast ( expr , domain [, criteria] )

The DFirst and DLast functions have these arguments:

Argument

Description

expr

Required. An expression that identifies the field from which you want to find the first or last value. It can be either a string expression identifying a field in a table or query, or 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.

domain

Required. A string expression identifying the set of records that constitutes the domain.

criteria

Optional. A string expression used to restrict the range of data on which the DFirst or DLast 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 DFirst and DLast functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DFirst and DLast functions return a Null.

Remarks

Note:  If you want to return the first or last record in a set of records (a domain), you should create a query sorted as either ascending or descending and set the TopValues property to 1. For more information, see the TopValues property topic. From a Visual Basic for Applications (VBA) module, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.

Examples

Expression

Results

SELECT DFirst("DateofSale","ProductSales") AS Expr1 FROM ProductSales GROUP BY DFirst("DateofSale","ProductSales");

Returns the random value from the field "DateofSale" of the Table "ProductSales". If the table has a "primary key", it will return the "FIRST" value of the "DateofSale" based on ascending order of primary key.

SELECT DLast("DateofSale","ProductSales") AS FirstDis_ID FROM ProductSales GROUP BY DLast("DateofSale","ProductSales");

Returns the random value from the field "DateofSale" of the Table "ProductSales". If the table has a "primary key", it will return the "LAST" value of the "DateofSale" based on ascending order of primary key. returns the First "ProductID" from the Table "ProductSales" where the "Discount" value is "0" and displays the result in the column "FirstDis_ID".

SELECT DLast("ProductID","ProductSales","Discount=0") AS LastDis_ID FROM ProductSales GROUP BY DLast("ProductID","ProductSales","Discount=0");

Returns the Last "ProductID" from the Table "ProductSales" where the "Discount" value is "0" and displays the result in the column "LastDis_ID".

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×