Applies ToAccess for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Return a field value from the first or last record in the result set returned by a query.

Syntax

First( expr )

Last( expr )

The expr placeholder represents a string expression identifying the field that contains the data you want to use or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

Remarks

These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in no particular order.

Examples

Expression

Results

SELECT First(DateofSale) AS Expr1 FROM ProductSales;

Return the first value from the field "DateofSale" in the table ProductSales.

SELECT Last(UnitPrice) as LastPrice, First(DateofSale) AS FirstDate FROM ProductSales;

Return the Last value from the field "UnitPrice" and First value from the field "DateofSale", displays them in column LastPrice and FirstDate respectively.

Calculating Fields in SQL Functions

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.