The order data is saved in a table.....

[Sun_Force]

I've seen several posts here that suggests a table is a container for data and there's no actual order in the saved data. A table is just like a bucket that keeps an amount of data. If the order of data matters, a query should be used instead.

Not that I doubt the experts here, but I simply was thinking why Microsoft has this definition of dlast :

You can use the DLast function to return a random record from a particular field in a table or query when you need any value from that field.

While I understand there's a random phrase there also, but isn't the word dLAST misleading?
If dLast can not return the actual last record in a table under a certain criteria, then why they chose Last? I mean DMin, DMax, Dlookup makes sense. Why not DRanodm instead of DLast?

And then, how DLast differs from DFirst? (if both return random records).

 

[The_Doc_Man]

Actually, those two Domain Aggregate functions make perfect sense - if you realize that they work on queries that have ORDER BY clauses. DFirst and DLast in an ordered recordset will work exactly as you would think they do. It is only for tables that they don't work as you might have hoped. But in fact, it isn't the fault of DFirst and DLast - it is the fault of the tables.

Here is the mechanism. Imagine that you have a sorted Excel data set that you import into Access and load it for the first time. OK, after that import, the new table should be sorted the same way as it appeared in Excel. But now let's update some records chosen at random. What is ACTUALLY stored in a table is a set of records. It is not clear as to the actual method of storage because Microsoft has not published Access Internals that often, but I personally believe that each table is actually a double-ended queue. The ".MoveNext" and ".MoveLast" operations would be most easily supported by such a structure. (I know that's not proof - it's just a suggestion based on my inference.)

Let's say you now update several records, almost at random. If you use the db.Execute query, dbFailOnError and an error occurs, you have to be able to roll back everything. So because of the need for the ability to be able to roll back changes, Access CANNOT just modify the records in place. It must COPY each record in turn, update it, and finally attempt to thread everything back into the table. The easiest way to do that is to thread it into the end of the table rather than trying to find fore-and-aft linkages to other records. Things will be threaded back in the chronological order in which they were modified - which leads to pseudo-randomization. (Remember, we said "update almost at random.")

Why put them at the end? (You ask....) Because tables are defined as UNORDERED recordsets. So it doesn't MATTER to the definition of the table that you don't maintain the original order. So it is trivially easy to append the changed record to the end of the double-ended list that is the physical table. The only tricky part is removing the old record from the list - which is not hard for a double-ended list structure. But in any case, after you do this often enough, you would realize that the actual order of records in a table is ascending chronological order of creation or update. Why WERE the records in order when you first created the table? Because that was also the chronological order of presentation - because in my set-up, I said it was a sorted Excel datasheet.

Now, let's ask the question: Do DFirst and DLast do anything unexpected? No, they just open the indicated recordset and do a .MoveFirst or a .MoveLast on it, pick up the requested field, and close the recordset, exactly like you might expect. What they GET depends on the recordset they opened, and as we all know, both tables and SELECT queries produce recordsets equally well. But only one of those two sources is guaranteed to be in a particular order.

 

[Sun_Force]

@The_Doc_Man, I understand and am with you in all those excellent and in detail explanation.
But still I think Microsoft has to remove mentioning "table" in definition of a method where it doesn't behave as expected.

No matter it's DLast's fault or the table's fault, it's obvious that it doesn't return the last data if the domain is a table.
It took a lot of my time, to experience and then search this forum to understand how and why DLast doesn't return what I need. And I'm sure I'm not alone in this and there's been (and will be) so many others who have faced this situation.

If only there was a "Doesn't work on tables" , it could have saved me (or possibly other fresh starters) a lot of time.

 

[Minty]

IMHO DLast and DFirst are horrible functions and cause no end of confusion because, in many circumstances, as you have discovered they don't do what you would expect.

They don't really do anything that can't be achieved in a more reliable way with other functions and or a query.
I say scrap them. #DictatorshipsR'Us#

 

[arnelgp]

tables are binary, and there are structures in them.
if you view your table what is the last record?
if you use dlast(), do you get the same last record or random one?
same with dfirst().
they refer to the way they are ordered in the structure.

records are saved in no particular order, yes.
but when you view it, is it topless/bottomless to say there is no First or Last on the structure?

 

[Sun_Force]

records are saved in no particular order, yes.
but when you view it, is it topless/bottomless to say there is no First or Last on the structure?

When I create a linked table to a sql server table with 130,000 records and open the linked table in Access, The PK field is not in A-Z order. It's random.
When I work on a local table in Access, the PK is started from 1 and ends to 3587. But Dlast doesn't return the fields of 3587th record.
I even didn't bother to search where the results come from.

if you view your table what is the last record?
if you use dlast(), do you get the same last record or random one?
same with dfirst().

Dlast result is the same record over and over. I'm checking the DLast in an input form. After adding several records, then the result of Dlast changes. But still it's not the last one.
And it continues to be the same. But after adding several more records, Dlast changes again. Still not the actual last record I've saved.
If it's a local table and I compact the file, Dlast starts showing the last record. But after several inputs, it changes again.

 

[The_Doc_Man]

it's obvious that it doesn't return the last data if the domain is a table.

But DLast actually DOES return the last record in the table. The last CHRONOLOGICALLY. Let's face it - there are many ways to view "order" and "order of data entry" is one of them. Too many people see table insertion as following "content order" when it doesn't.

I'll concede that not every bit of documentation hammers this point home. Their words might be misleading. But if you read the documentation, you would see that there is a strong disclaimer about what those functions can be expected to do.

This one talks about returning a "random" record:

DLast Method - Microsoft Access Visual Basic Documentation

documentation.help

This one also uses the "random" record language and has remarks about using an ordered query if you want something in a particular position with respect to the dataset.

Application.DLast method (Access)

Office VBA reference topic

docs.microsoft.com

This article gets explicit about record order expectations.

ORDER BY - First, Last and always!

Sorting the results of SQL Queries is trivially simple but you need to do it explicitly to avoid unexpexted results.

codekabinett.com

In particular, note that there is a discussion about record storage order and the fact that new records are normally just tacked on at the end of the table.

Part of the confusion is, as I said in my earlier post, that we don't have a description of the internals of the DB, so we cannot tell exactly HOW the engine chooses the first, last, next, or previous record. All we know is that it does. That's the problem with black-box systems.

 

[Gasman]

I even didn't bother to search where the results come from.

Well according to what you have just said, all you have to do is return the PK with the Dlast() ?

 

[arnelgp]

But Dlast doesn't return the fields of 3587th record

are you sure?
i view the last record (table view), the id is 18737
when i dlast it (dlast("id", "mytable")) it returns same number.

each records are not save (physically) Sequencially. they are in random
and are only connected by their pointer. just like a tree with branches
and leaves.

 

[Sun_Force]

are you sure?
i view the last record (table view), the id is 18737
when i dlast it (dlast("id", "mytable")) it returns same number.

 

[Gasman]

View attachment 96452

Now repeat those lines

 

[arnelgp]

In the pics, you have some sorting on the first 2 fields. Use dlast without sort.

 

[Sun_Force]

In the pics, you have some sorting on the first 2 fields. Use dlast without sort.

The result is the same. I'm away from the pc with that database. I will send a photo without filter as soon as I get back to that database.

Did you see the comment in my first post? It's a copy and paste from Microsoft documentation on DLast.
It explicitly says DLast returns a random value not the last one.
If you are receiving the last record of your table, it's your lucky day. Buy some lottery tickets. You may win

 

[Sun_Force]

Now repeat those lines

As I said the result is the same. Until I save several (5 or 6) records. Then it changes to another rondom result.

 

[CJ_London]

it explicitly says DLast returns a random value not the last one.

It doesn't say it does not return the last value - it does. The randomness comes from the value coming from the last record which changes (or not) as records are added or amended per Doc's explanation - But that last value is not necessarily from the last record appended.

You're not confusing a dlast value with a movelast record?

 

[Sun_Force]

........The randomness comes from the value coming from the last record which changes (or not) as records are added or amended per Doc's explanation - But that last value is not necessarily from the last record appended.

You're not confusing a dlast value with a movelast record?

Programming languages are made by human, for human, according to human senses.
We've been taught Maximum is the largest number in a range of values. If I've been given DMax to search for a maximum value of a field
I expect DMax("myField","Mytable") return the largest number.(which does). I don't care how the engine works, I don' care how Access manipulates data behind scene. I don't care what is under the hood. As long as it returns the correct value, I'm satisfied. If DMax in different situation may not return the largest number, I expect it's been explained clearly in its documentation.

If I have been given a DLast method, I expect it to return the last record. Because it's what Last means. If for any reason DLast doesn't return the last value, then it's not DLast anymore. It's something else. I know Doc believes Last MAY not be the order of adding data, but Microsoft doesn't explain it clearly. It doesn't even say it's the last modified data. It says a random record. A random record according to our common sense is not the last record (even if it maybe the last modified record). If you give me a knife, I expect it to cut. If for any reason it doesn't cut, it's not a knife anymore. It's a new tool. And I expect not to be called knife.

I've seen many programmers use DLast with a combination of DMax. Because (as Minty explained in #4) it's not reliable.
Something in the lines of:
DLast("ThisField","MyTalbe","PKField=" & Dmax("PKField","MyTable")

I still believe the documentation is poor and should be corrected and explain that DLast doesn't return the last added record when the domain is a table.

 

[CJ_London]

I have been given a DLast method, I expect it to return the last record.

dlast does not return a record, it returns a field value

You talk about engines so I'll use an analogy

a train leaves a station on a single track railway with three carriages A, B and C in that order - the last carriage to leave the station is C - think you will agree with that.

it arrives at the next station, disgorges its passengers and returns (having to go backwards as there is no facility to turn the train round) - the last carriage to leave that station is A

The train company has a lot of passengers so they add another carriage D. But due to methods of coupling them together it has to go between carriages A and B. This does not change the last carriage to leave the station, even though it is the last carriage to be added to the train.

I agree the statement here

Application.DLast method (Access)

Office VBA reference topic

docs.microsoft.com

says (as you quote)

You can use the DLast function to return a random record from a particular field in a table or query when you need any value from that field.

is wrong in the sense that it doesn't return a record, it returns a field value.

but it does say further on

Remarks​

Note
If you want to return the first or last record in a set of records (a domain), you should create a query sorted as either ascending or descending and set the TopValues property to 1. From Visual Basic, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.

So think your beef is around what Last actually means - from the train analogy which single carriage would you deem to be last in all scenarios?

 

[MajP]

If dLast can not return the actual last record in a table under a certain criteria, then why they chose Last? I mean DMin, DMax, Dlookup makes sense. Why not DRanodm instead of DLast?

If you have a PK (which you should) then it returns a value from the First/Last record sorted by the PK in ascending order. Maybe that is where they pulled the name from. In my case almost all may tables use an autonumber PK so the vast majority of the time Dlast is the last record added and Dfirst is the first record added.

If the table has a "primary key", it will return the "Last"/"FIRST" value of the selected "field" based on ascending order of primary key.

 

[Sun_Force]

@MajP seems that I had missed the remark section of the documentation. I will take a look once again.
thanks for clarifying.

 

[The_Doc_Man]

On the other hand, one of the examples I found shows that if you add an index on another field in the table, it can change which record is involved from DFirst/DLast if neither the PK nor the newly indexed field is the one you are retrieving from a table.