DLast not showing correct values

[Peter Bellamy]

I know that Dlast DFirst etc do not always return the first and last record if you use them on a table, but they are supposed to work on a query that has been sorted.

However I have one that doesn't!
There are 3685 records all shown in the correct order when I view the query but the DLast command consistantly comes back with it the 3430th line ??

The code is 'constructed' to enable different buttons to provide the keywords that the 'GetRecords' routine requires.

Thanks

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

'Stop

tablename = "QryAndrewsCWH"
tableshname = "andrews"
prodname = "CWH"
Me.customr = "Andrews"

GetRecords
tablename = "Andrews CWH"
StartSerialNo.SetFocus


Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

Public Sub GetRecords()

SNo = tableshname & "_serialno"
prod = tableshname & "_product_type"
proddate = tableshname & "_date"
PackDate = tableshname & "_date_stock"
Tester = tableshname & "_tester"
invoice = tableshname & "_invoice_no"
Notes = tableshname & "_notes"
cust = tableshname & "_customer"

Me.firstsno = DFirst(SNo, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.firstprod = DFirst(prod, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.firstdate = DFirst(proddate, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.lastsno = DLast(SNo, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.lastprod = DLast(prod, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.lastdate = DLast(proddate, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")
Me.number = DCount(SNo, tablename, "[" & tableshname & "_customer]  = '" & Me.customr & "'")

 

[nathansav]

Cant really advise without seeing, can you attach a zipped DB?

 

[vbaInet]

It gets a random record. The first or last is not guaranteed. Use DMin() and DMax()

Also, I can see that all your statements are pulling from the same table. Why not use a recordset?

 

[Peter Bellamy]

What is the point of DFirst and DLast if it does not work on a sorted query !!
Will DMin and DMax be any more reliable?

I have been considering changing to recordsets as I think it will be faster, it is just that I wrote this a while ago and don't revisit it unless there is a problem.

 

[vbaInet]

Yep, change the DFirst to DMin and the DLast to DMax.

And yes, recordset will be the faster option.

NB: I don't think anyone uses DFirst or DLast unless you're not worried about what record it returns

 

[Peter Bellamy]

Changed it to a recordset and it is correct, and much much faster
Thanks

 

[vbaInet]

Glad you got it working.

 

[gemma-the-husky]

out of interest, i have never used dfirst/dlast

i just checked help, and it does say they return a random record - seems a strange naming convention for something that doesnt do first or last, doesnt it!

 

[pbaldy]

More info:

http://support.microsoft.com/kb/208190

 

[vbaInet]

It begs the question why such functions exists. Has anyone used it in any real life app?

 

[LPurvis]

Just trying to find threads where I've touched on the First/Last (or their Domain function equivalents). Not as easy to find as I'd expected.
Here's a couple from across the way.
http://www.utteraccess.com/forum/DLOOK-latest-entry-t1944015.html
http://www.utteraccess.com/forum/aggregate-group-query-t1884972.html

Cheers.

 

[gemma-the-husky]

actually it didnt say it returned a random record - it returns the first or last record added.

so i guess it would have a use - lets say you insert a record with a insert into statement, and then want to retrieve the autonumber that the insert created.

the dlast would find that, as it is the last item added.

-----------
i think this is equivalent to the @@identity function (i think that is the right expression - but is that SQL only?) that others have pointed out as useful for a similar use.

 

[vbaInet]

Dave I thought you said you checked the help files and it did say it returned "random records"? Some excerpts from the help file (with keywords in red):

DLast:
Access Developer Reference Application.DLast Method

Show All​

Hide All​

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

Or First, Last functions:
Remarks

The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

I would be inclined to use a Recordset to get the last autonumber "I" inserted into the table.

 

[LPurvis]

Just to be clear, I'll re-iterate from the threads I pointed to and expand based on memory.
The First and Last functions are descriptive of what they do. But it is not what is commonly perceived or expected.
They return the value from the first or last selected row.
This is the row as it is read by the database engine.
This will be in primary key order of the table.

If an autonumber field of a table is also the primary key (i.e. what is the clustered index in Jet/ACE) then it will be the corresponding first or last occording to that field's ordering.
The order of any query they are contained within is irrelevant. As is any query upon which you may attempt to base them upon.
There is some similarity to the results of @@Identity in this behaviour - but it's not the same. Oh, and yep, @@Identity is valid in Jet - introduced with the dawn of Jet 4 - trust me, I wouldn't have mentioned it in the linked thread if it weren't ;-). Though you might be thinking about Scope_Identity() which is not supported in both - only T-SQL.

If the autonumber field is not the primary key, then the results will adhere to whatever is. If there is no primary key then (after some sort of ritual flagellation cerimony for punishment of that crime) then it would most likely default to order entered (and therefore on disk) but a miriad of things could potentially interfere with that - and we're into apparent randomness. (Emphasis on the "apparent" of course!)

When a user requests aggregated data but with "any" row from a detail column (sheesh) then the advantage of the First/Last functions is that each separate such function call will still result in data from the same row. Unlike Min, Max etc - which return values per column.

Cheers.

 

[gemma-the-husky]

for clarification

this came from A97 help

Microsoft Access DLAST F1 help

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. Use the DFirst and DLast functions in a in a macro, module, query expression, or calculated control on a form or report.

but then i found THIS support article

http://support.microsoft.com/kb/109380

which i think gives a different explanation to those mentioned above. Anyway, i have never used dlast - but as I say, it may be useful to retrieve data from the last record added.

from the article

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.

 

[vbaInet]

That article actually refers to much older versions of Access, i.e. 1.0, 1.1 and 2.0 as it states, so I would imagine the much relevant reference would be that for recent versions.

http://support.microsoft.com/kb/208190/en-us
http://office.microsoft.com/en-us/access-help/dfirst-dlast-functions-HA001228823.aspx

LPurvis has covered much of this anyway.