ACC2000: First and Last Functions Return Unexpected Records

ID: Q208190

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

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 given sort order.

RESOLUTION

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

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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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:

In the Database window, click Queries under Objects, and then click New.

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

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

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

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

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.

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:

In the Database window, click Queries under Objects, and then click New.

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

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

In the Categories dialog box, double-click CategoryID.

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

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

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:

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

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.

Additional query words:

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