Alternative to DLookup

  • Thread starter Thread starter L.A. Lawyer
  • Start date Start date
L

L.A. Lawyer

I have read, here and elsewhere, to avoid dlookup for a variety of reasons.

Then what is the alternative(s)?
 
L.A. Lawyer said:
I have read, here and elsewhere, to avoid dlookup for a variety of
reasons.

Then what is the alternative(s)?
Click to expand...

Often the advice to avoid Dlookup() is overblown. Domain aggregate
functions are not inherantly slow in retrieving data compared to other
methods, but they do incur some background overhead each time they are used
which makes them a bad choice to use in queries or in code looping
operations. If you just need to use one or two of them on a non-continuous
form or in a piece of code where they are not used over and over in a loop
then they are fine.

In a query I would normally see if I could use a sub-query instead and in a
code loop I would use a Recordset where the overhead I am describing above
is performed outside the loop and thus is only incurred once instead of on
every loop iteration.
 
DLookup() is perfectly appropriate for looking up a single value from a
table. Use it. The only programmatic flaw with DLookup() is that it fails to
distinguish between a Null and a zero-length string.

If you want to do it on every row of a *query*, it would be much better to:
a) Add the lookup table to the query, or
b) Use a subquery.

For a), use an outer join. Details in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

In subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
If the subquery outputs a value, the results will be read-only.

In *code*, where you need to lookup values repeatedly (in a loop), the most
efficient approach would be to OpenRecordset using a SQL statement that
selects ony the records you want, sorted as you want. You can then walk the
recordset as needed. Because it remains open, this is hugely more effient
that opening and closing recordsets or making multiple DLookup() calls.

This link contains a replacement for DLookup():
http://allenbrowne.com/ser-42.html
While it is more efficient than DLookup(), it is still much slower than the
alternatives given above. The main advantage of the ELookup() function is
that it lets you specify how to sort the records to retrieve, whereas
DLookup() is pot-luck (i.e. you just get whichever value is first in the
set, without being able to specifiy how the records are sorted.)
 
I have read, here and elsewhere, to avoid dlookup for a variety of
reasons.
Click to expand...

Like the other posters, I also disagree that DLookup is a Bad Thing when
finding a single value from a single record.

It's not good when looking up single values from multiple records (see
Allen's and Rick's posts). The other place where it's not good is looking
up multiple values from a single record e.g.

' don't do this!
sFName = DLookup("FName", "Employees", "EmpID=1024")
sLName = DLookup("LName", "Employees", "EmpID=1024")

' ... do this
' only takes one trip to the server and it's likely that
' both values are on the same disk page.
jetSQL = "SELECT LName, FName FROM Employees " & _
"WHERE EmpID=1024"
Set rst = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)
' error trapping goes here...
sFName = rst!FName
sLName = rst!LName



It's slightly more work to type the program, but your users and network
admins will love you for it...

Best wishes


Tim F
 
Back
Top