Microsoft KB Archive/109380

= ACC: DFirst and DLast Functions Return Unexpected Records =

Article ID: 109380

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q109380



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



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



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: kbusage KB109380

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.