Article ID: 208190 - View products that this article applies to.
This article was previously published under Q208190
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
In the Totals row of the query design grid, you can choose the First() or Last() function to return the value of a specified field in the first or last record, respectively, of the result set returned by the query. However, these functions may appear to return arbitrary values because the records are returned in an order that may not be apparent.
Additionally, in an expression, the First() and Last() functions do not return the first and last records of the specified table or query, as you would expect. If the source is a query, the First() and Last() functions appear to ignore the sort order of the query. If the source is a table, the First() and Last() functions appear to ignore the order of the current index or primary key.
Likewise, the DFirst() and DLast() functions do not return the first and last records of the specified domain, as you would expect.
This article describes methods that you can use to return the first and last values based on specific criteria.
The First(), Last(), DFirst(), and DLast() functions ignore sort orders, indexes, and primary keys. These functions return the first or last undeleted record based on the order in which the records were entered into the table, not the first or last record in a specified sort order.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. In some cases, compacting the database may return the results that you want. To ensure that the results will always be consistent, use one or more of the following procedures.
In order to obtain the expected first and last records, you should organize the recordset data in a predictable sequence. You can do this by using one of the following methods.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
Method 1If you are looking for the highest and lowest values in the result set, use the Min() and Max() functions in place of the First() or DFirst() and Last() or DLast() functions. For example, to get the highest and lowest freight in the Orders table, follow these steps:
Method 2Another way to get the first and last records in a result set is to sort the query in ascending order (or descending order if you want the last record), and to set the TopValues property to 1. For example, to get the last CategoryID in the Categories table, follow these steps:
Method 3A third way to get the first and last record in a result set is to open a recordset based on a query in DAO, and then to use the MoveFirst or MoveLast methods to get the first or last record.
To display the first and last Order date in the Orders table of the sample database Northwind.mdb, follow these steps:
The "Remarks" section of the Help File topic, "First, Last Functions" incorrectly states that unless the query includes an ORDER BY clause, the order of records returned will be arbitrary. The correct information is that the order of records returned by the First() and Last() functions is not affected by the ORDER BY clause.