ACC2000: First and Last Functions Return Unexpected Records (208190)

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