Full Version: DLast Function
UtterAccess Forums > Microsoft® Access > Access Forms
Beerman3436
I am trying to get the last record in a table only. I am using this code:
Dlast("Field","Table")
Does it matter is there a NULL values in the Field column?
Oalso tryed this with no luck
Dlast("Field","Table","Field" is not null")
fkegley
I usually do this with the Max function. You will need to have some field that can be used for this. For instance, an Sequential AutoNumber (Rob, I remembered) as the primary key, the last record entered would be the one with the max AutoNumber value. Or if you are recording the date on which each record was entered, you could use a max on that field.
schroep
Last is not very reliable, especially when performed directly on a table. Even if you have applied a "table sort", that's just presentation; order doesn't actually mean much to a database unless it is specified.
You may have better luck with DLAST on a QUERY where you specifically tell Access how it should be sorted. If you have as your primary key an incrementing autonumber (highly recommended), simply sort on that primary key field.
Other solutions involve using DMAX instead if you want to use domain functions.
Beerman3436
I tryed Dmax but when I use it I get ever record in my table listed in the query with the value of the last record in each field. I only want 1 result, the last record in the table.
schroep
Are you using domain functions (DLAST, DMAX) in a query?
There are much better ways to do it.
Can you post the full SQL of your query, along with a list/description of the fields in your table? We can help you get this working.
CAn example would be:
Select * From tablename Where [idfield]=(Select Max([idfield]) From tablename)
Beerman3436
Ok I want to use this on a form. I need it to lockup the last value that was put into the table.
This works in a query but will not work for the Control Source of a text box on the form.
schroep
To use domain functions like this, you combine a DMAX with a DLOOKUP. For example:

=DLOOKUP("fieldname","tablename","[idfield]=" & DMax("[idfield]","tablename"))

This says:

Give me [fieldname] from [tablename] where the [idfield] is equal to the maximum [idfield] in [tablename].
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.