dlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dlookup with if 1
dlookup 2
DLOOKUP question 3
Dlookup error 4
Design question combo or dLookup 1
DLookup debacle 4
DLookup in Text box to show Currency (number field) 9
Trouble with DLookup 3

Back
Top