ACC97: First and Last Functions Return Unexpected Records

ID: Q189391

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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 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 expect.

This article describes methods that you can use to return the first and last values based on specific criteria.

Parts of this article assume that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.


CAUSE

The First(), Last(), DFirst(), and DLast() functions ignore sort orders and indexes, and primary keys. These functions are intended to return data from the first or last undeleted record entered into the table, not from the first or last record in a given sort order.


RESOLUTION

In some cases, compacting the database may return the results 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 any of the following three methods.

NOTE: The following examples use the sample database Northwind.mdb.

Method 1

If 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:
  1. In Database window, select the Queries tab and click New.


  2. In the New Query dialog box, select Design View, and click OK.


  3. In the Show Table dialog box, select Orders, click Add, and then click Close.


  4. In the Orders dialog box, double-click Freight twice.


  5. On the View Menu, click Totals to insert a Total row in the query grid.


  6. There should be two Freight columns in the query grid as a result of step 4. In the first Freight column, click in the Totals row and select Min. In the second Freight column, click in the Totals row and select Max.


  7. On the Query Menu, click Run.


If you have not made any changes to the sample database Northwind.mdb, the values returned should be $0.02 and $1,007.64, the smallest and largest Freight values in the Orders table.

Method 2

Another 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:
  1. In Database window, select the Queries tab, and click New.


  2. In the New Query dialog box, select Design View, and click OK.


  3. In the Show Table dialog box, select Categories, click Add, and then click Close.


  4. In the Categories dialog box, double-click CategoryID.


  5. In the first column in the query grid, click in the Sort row and select Descending.


  6. On the Query Design toolbar, type 1 in the Top Values combo box.


  7. On the Query menu, click Run.


If you have not made any changes to the sample database Northwind.mdb, the value returned should be 8, the last CategoryID in the table.

Method 3

A third way to get the first and last record in a result set is to open a recordset in DAO based on a query, and then to use the MoveFirst or MoveLast methods to get the first or last record.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
To display the first and last Order date in the Orders table of the sample database Northwind.mdb, follow these steps:
  1. In a new module, type or paste the following code: 
    
    Sub GetFirstRecord()
      Dim DB As Database, RS As Recordset

      ' Use the currently opened Northwind database.
      Set DB = CurrentDb

      ' Open the Orders table as a Dynaset Type Recordset.
      Set RS = DB.OpenRecordset("Orders", dbOpenDynaset)

      ' Move to the first record in the recordset.
      RS.MoveFirst
      MsgBox "The first order date is " & RS!OrderDate

      ' Move to the last record in the recordset.
      RS.MoveLast
      MsgBox "The last order date is " & RS!OrderDate

      ' Close the recordset and database.
      RS.Close: DB.Close
    End Sub


  2. With the pointer anywhere within the code you entered in step 1, run the subroutine by pressing the F5 key.


If you have not made any changes to the sample database Northwind.mdb, you should receive one message box with the first Order Date (4/8/94), and a second message box with the Last Order Date (6/5/96).

NOTE: If you do not include the dbOpenDynaset in the Set RS line of code the records will be returned directly from the table and first record will be arbitrary. For example, if the Set RS line is
Set RS = DB.OpenRecordset("Orders")

then the OpenRecordset method defaults to a Table Type recordset and the MoveFirst method should return the date 11/16/64, which is not from the first record displayed in the Orders table.


MORE INFORMATION

The Remarks section of the Help File topic, "First, Last Functions" incorrectly states that unless the query includes and 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. 


Keywords          : kbdta QryOthr 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: July 6, 1999