ACC2000: First and Last Functions Return Unexpected Records

Article translations Article translations
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).

Expand all | Collapse all

On This Page

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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 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 the Database window, click Queries under Objects, and then 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 the Database window, click Queries under Objects, and then 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 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 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:
  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 that you entered in step 1, run the subroutine by pressing F5.
If you have not made any changes to the sample database Northwind.mdb, you should receive one message box with the first Order Date (7/4/96), and a second message box with the Last Order Date (5/6/98).

MORE INFORMATION

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.

Properties

Article ID: 208190 - Last Review: June 23, 2005 - Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbprb KB208190

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com