DFirst function question

[Jim Burke in Novi]

I have a simple query, which selects both fields from a two-field table and
sorts them in descending order. NO criteria or anything. Obviously when I run
the query I see the sorted results. But when I use the DFirst function to get
the value for one of the fields from the first row in the query, it returns
one of the last values! Is this function really supposed to return the value
from the first row? Some of the doc I read says it returns a random value,
which to me makes no sense. I had to change my code to open the query via a
recordset, move to the first row and get the values that way, which works
fine.

 

[Jim Burke in Novi]

The problem is that DFirst is not returning the first row. I changed my code
to use .MoveFirst on a recordset because DFirst wasn't working. And I found
multiple explanations about DFirst on the net that says it returns a random
value, and am wondering why that doc is out there.

 

[John Spencer]

Because for practical purposes DFirst and DLast essentially do return a
"random" record - either the first or last record found based on the criteria
you have input. Supposedly, you can generate the actual first or last record
based on sort order, but I have never really tested this out.

Sounds like an interesting problem to experiment on when I do have some time.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[John Spencer]

Some quick and limited testing indicates the DFirst and DLast were returning
the record based on the Primary Key in the table - ignoring any sorts I
imposed. If I break the rules and test a table with no primary key, then
first and last are whatever record appears in those positions when I display
the table.

In other words, I would use DFirst, DLast, or First and Last (in a query) only
when I wanted to get a record in the group and did not care which record I
happened to get. The good thing is that First and Last do return the fields
from the same record when used on multiple fields.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County