DLast not getting last record

[Ron99]

I have used a form for years and DLast worked. But all of a sudden DLast is returning a record in the middle of the table from 2 years ago. One is a text box. One is a List Box. Both are getting the wrong record. All other forms using DLast on different tables are working OK.



I haven't changed the form.
=DLast("[DebitCredit]![DCDate]","[DebitCredit]")

What happened?
Thanks for all help.

[JoeM]

I never use the DLAST function. Notice what it says about it on the Microsoft site (first line here: http://office.microsoft.com/en-us/ac...001228823.aspx)

You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field

Also, be sure to read the Remarks at the end of that link.

If you want to return a particular record, you should use other functions, such as DMAX.

For further explanations on how DLAST really works, see this link too: http://support.microsoft.com/kb/103403

[Ron99]

That link also says this

An expression that identifies the field from which you want to find the first or last value.

DLast worked for 8 years on this form. It's working on all other forms. What would happen to make it not work on a table?

[JoeM]

Did you read the links I provided so you gain a clear understanding of how DLAST actually works? It may be coincidental, or the way your other projects worked was consistent with how DLAST works, and maybe this one isn't.

What exactly are you trying to return?
What do you classify as your "last" record?
Do you records include an Autonumber field or some other primary key?

[Ron99]

Did you read the links I provided so you gain a clear understanding of how DLAST actually works? It may be coincidental, or the way your other projects worked was consistent with how DLAST works, and maybe this one isn't.

What exactly are you trying to return?
What do you classify as your "last" record?
Do you records include an Autonumber field or some other primary key?

I know how DLast works. I have been using it for 8 years.

Yes I use an Autonumber field.

I want the date from the last record entered in the table. From the record with the highest number in the Autonumbered field.
=DLast("[DebitCredit]![DCDate]","[DebitCredit]")

The highest ID number is 927. It is returning the date from a field numbered somewhere between 533-547.

[JoeM]

I know how DLast works. I have been using it for 8 years.

Those are two mutually exclusive statements (really, I am not trying to pick a fight here, just pointing out the facts). I have some very complex code myself that I downloaded from the code years ago that I use in various projects, that I have no idea how it really works.

If you really know how DLast works, you know that it is only guaranteed to return a RANDOM record. This is because it is important to understand how Access orders (or does not order records). Someone once described as Access table as a "bag of marbles" - order really has no meaning to Access. I suppose that they are in some sort of order as they are first entered, but doing things liking editing or deleting records and/or compacting the database can change all that. So after time, Access may not view the records to be in the order that you think they are. That is why DLAST is very unreliable if you want to pull a particular record instead of a Random record.

As the "Remarks" said, you can use a Recordset to order your records, and then loop through it to get what you are after. That kind be a bit cumbersome. For what you want to do:

I want the date from the last record entered in the table. From the record with the highest number in the Autonumbered field.

I would recommend using the DMAX function in conjunction with the DLOOKUP function. You can use the DMAX function to return your highest Autonumber field, and then use the DLOOKUP function to return the date associated with that particular record.

[ch5150]

Hi,
I am having difficulty in finding a solution that seems to be a DLast issue. I have a subtable/form that has a field that will increment by 1 for each new record. (I haven't figured out how to make this start at 1 for each new JobID so at this point it is being manually input.) The expression I have written is: DLast("Hour","tblProductionByHour","JobID=" & [JobID])
In the end I will have to average the "sets per hour" by totaling the total number of sets completed by the "Hours" taken. So I am trying to extract the value of the "Hour" field in the last row of tblProductionByHour where JobID=JobID.

[orange]

ch5150,

You have attached your post to a thread that is 2+ years old.

See this M$oft article re DFirst and DLast
which says , in overview,

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

[ch5150]

Hi,
Thanks for your response. The page you referenced did not add to my understanding of the function. I did however, see a suggestion to use a query and set the TopValue property. I have been unable to find that property to set it. Any information you can provide would be greatly appreciated.
Sincerely,
ch5150

[orange]

I don't understand exactly what this means

DLast("Hour","tblProductionByHour","JobID=" & [JobID])
In the end I will have to average the "sets per hour" by totaling the total number of sets completed by the "Hours" taken. So I am trying to extract the value of the "Hour" field in the last row of tblProductionByHour where JobID=JobID.

What I can say is that DLast does not get you the last (latest) record in a table.

If you have a table whose records contain, say FinishedDate. You can find the record with the latest entry (logically the last record in the table) using a query such as:

Select yourfield1, yourfieldX from YourTableName
Where FinishedDate =(Select Max(FinishedDate) from yourTableName)

Here the subquery (green) retrieves the latest FinishedDate. The base query finds the record in the table whose FinishedDate equals that maximum value.

[ch5150]

Thanks for your input. The expression was meant to retrieve the last entry of the "Hour" column in the "tblProductionByHour" subtable using the JobID as a filter. I don't actually need the record from the table, only the value of "Hour" in the last record.
So "Select Max(Hour) from tblProductionByHour" should retrieve the contents of the last record in the Hour column? Since the entries of that field are incremental the largest value will always be the latest or last record.

[ssanfu]

Since the entries of that field are incremental the largest value will always be the latest or last record.

Nope. Not even close.
A table is a "bit bucket". It has no inherent order. Most of the time the "last" record will be the latest or last entered record, but not always. And it depends on your definition of what "Last" represents.
That is why queries are used. You can set an order and get the "last" record for that recordset and sort order.

[orange]

ch5150,

How about posting a copy of your database? Just enough records to show your data.

As Steve says a table is a bit like a bag of marbles - when you add multiple records/marbles there is NO INHERENT ORDER. Any order is attained through the use of a query with an ORDER BY against the table.

From wikipedia:
The relational model specifies that the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes. Applications access data by specifying queries, which use operations such as select to identify tuples, project to identify attributes, and join to combine relations.


Good luck with your project.

[Do.little]

Those are two mutually exclusive statements (really, I am not trying to pick a fight here, just pointing out the facts). I have some very complex code myself that I downloaded from the code years ago that I use in various projects, that I have no idea how it really works.

If you really know how DLast works, you know that it is only guaranteed to return a RANDOM record. This is because it is important to understand how Access orders (or does not order records). Someone once described as Access table as a "bag of marbles" - order really has no meaning to Access. I suppose that they are in some sort of order as they are first entered, but doing things liking editing or deleting records and/or compacting the database can change all that. So after time, Access may not view the records to be in the order that you think they are. That is why DLAST is very unreliable if you want to pull a particular record instead of a Random record.

As the "Remarks" said, you can use a Recordset to order your records, and then loop through it to get what you are after. That kind be a bit cumbersome. For what you want to do:

I would recommend using the DMAX function in conjunction with the DLOOKUP function. You can use the DMAX function to return your highest Autonumber field, and then use the DLOOKUP function to return the date associated with that particular record.

Hi,
No Idea if this is the right way to reply; I'm trying.
DMax works fine.
Suppose table is Calls; CallId the (incremental) "autonumberfield", "Last CallerId" the field you need.
In VBA:
Dim Var1, Var2

Var2 = DMax("CallId", "Calls")
Var1 = DLookup("CallerId", "Calls", "CallId =" & Var2)
Or: Var1 = DLookup("CallerId", "Calls", "CallId =" & DMax("CallId", "Calls"))
In a text-box: (In my version)
=DLookUp("CallerId";"Calls";"CallId=" & DMax("CallId";"Calls"))
Thanks