ACC: DFirst() and DLast() Functions Return Unexpected Records

Last reviewed: April 2, 1997
Article ID: Q109380
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

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

The DFirst() and DLast() functions do not return the first and last records of the specified domain as you expect. If the domain is a query, the DFirst() and DLast() functions appear to ignore the sort order of the query. If the domain is a table, DFirst() and DLast() appear to ignore the order of the current index or primary key.

CAUSE

DFirst() and DLast() ignore sort orders and indexes, even Primary Keys. These functions are intended to return data from the first or last record entered into the table, not the first or last record in a given sort order.

Microsoft Access online Help incorrectly states:

   DFirst and DLast return values from the first and last occurrence
   according to the order of records in domain. If domain is an indexed
   table, the order follows the current index. Otherwise, the order
   follows the actual order of the records.

RESOLUTION

The following two sample Access Basic functions, GetFirst() and GetLast(), can be used in place of the DFirst() and DLast() functions to return the first and last records in the sorted domain as you expect.

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Option Explicit

   Function GetFirst (Expr$, Domain$, Criteria$)
      Dim MyDB As Database
      Dim MyDyna As Dynaset

      On Error GoTo Err_GetFirst

      Set MyDB = CurrentDB()
      Set MyDyna = MyDB.CreateDynaset(Domain$)

      If Len(Criteria$) > 0 Then
         MyDyna.Filter = Criteria$
         Set MyDyna = MyDyna.CreateDynaset()
      End If

      MyDyna.MoveFirst
      GetFirst = MyDyna(Expr$)

   Bye_GetFirst:

      Exit Function

   Err_GetFirst:
      GetFirst = Null
      Resume Bye_GetFirst

   End Function

   Function GetLast (Expr$, Domain$, Criteria$)
      Dim MyDB As Database
      Dim MyDyna As Dynaset

      On Error GoTo Err_GetLast

      Set MyDB = CurrentDB()
      Set MyDyna = MyDB.CreateDynaset(Domain$)

      If Len(Criteria$) > 0 Then
         MyDyna.Filter = Criteria$
         Set MyDyna = MyDyna.CreateDynaset()
      End If

      MyDyna.MoveLast
      GetLast = MyDyna(Expr$)

   Bye_GetLast:
      Exit Function

   Err_GetLast:
      GetLast = Null
      Resume Bye_GetLast

   End Function

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database NWIND.MDB.

  2. Open the Employee List query. Note that the employee names are sorted by last name, with the name "Buchanan, B. L." listed first, with an Employee ID of 5.

  3. Open the Utility Functions (Introduction To Programming version 1.x) module in Design view, and then choose Immediate Window from the View

        menu.
    

  4. Type the following in the Immediate window, and then press ENTER:

          ? DFirst("[Employee ID]","Employee List")
    

Employee ID 1 is returned, not Employee ID 5, even though Employee ID 5 is first in the sort order. Employee ID 1 is the first indexed record in the table that the query is based on.


Keywords : ExrOthr kbusage
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbdocerr


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.