dlookup

[Guest]

I am using the dlookup function on a form to retrieve a value from a table,
from the last time this form was completed for a particular customer. This is
so that I can perform a calculation based on the old value and the new one
entered on the form.
However because there is more than one instance dlookup returns the first
value rather than the last.
I thought dlast may work but I'm being unsuccessful!
Any help?
Ian

 

[John Vinson]

I am using the dlookup function on a form to retrieve a value from a table,
from the last time this form was completed for a particular customer. This is
so that I can perform a calculation based on the old value and the new one
entered on the form.
However because there is more than one instance dlookup returns the first
value rather than the last.
I thought dlast may work but I'm being unsuccessful!
Any help?
Ian

DLast() is pretty nearly useless, in my experience. It returns the
last record *IN DISK STORAGE ORDER* - an order which is arbitrary and
uncontrollable.

You may need to use DMax() instead, if you're just looking up the
date; if you need a value other than the date, use DMax() within
DLookUp to find the desired value. Note that domain functions can be
slow, especially when nested - you may want to use a Query instead.

For DLookUp:

=DLookUp("[fieldname]", "[tablename]", "[CustomerID] = " &
[txtCustomerID] & " AND [CompletionDate] = #" &
DMax("[CompletionDate]", "[tablename]", "[CustomerID] = " &
[txtCustomerID]) & "#")

For a Query:

SELECT TOP 1 fieldname
FROM tablename
WHERE [CustomerID] = [Forms]![[YourFormName]![txtCustomerID]
ORDER BY [CompletionDate];

Save this query and use it with DLookUp, as it will return only one
record, the one with the latest date.

John W. Vinson[MVP]