[AccessD] Whilst on DLOOKUPs....

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


Thanks Everyone,  Appreciate your thoughts and feedback.

cheers
Darryl


________________________________________
From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock [Gustav at cactus.dk]
Sent: Tuesday, 10 May 2011 11:54 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Whilst on DLOOKUPs....

Hi Darryl

Jim is right, such constructions are awful.
But don't forget that retrieving computed or concatenated values in one call indeed is possible, like:

  =Dlookup("[LastName] & ', ' & [FirstName]","tblEmployee","[EmployeeID] = " & lngEmployeeID)

/gustav


>>> jimdettman at verizon.net 10-05-2011 15:46 >>>
Darryl,

  All the Domain functions are really encapsulated SQL statements, which can
be used where an expression is allowed, but an SQL statement is not.

  Because of that, it also means they should not ever be used in a SQL
statement.  The simple reason being is that the query optimizer cannot
optimize them and since they represent an SQL statement anyway, you should
be using the appropriate Join(s) and SQL statements.

  As for them always being slower, that is not the case.  A Dlookup() for
example can be as fast as other methods.  However it is inefficient to do
something like this:

  =Dlookup("[FirstName]","tblEmployee","[EmployeeID] = " & lngEmployeeID)
  =Dlookup("[LastName]","tblEmployee","[EmployeeID] = " & lngEmployeeID)
  =Dlookup("[MiddleName]","tblEmployee","[EmployeeID] = " & lngEmployeeID)

  If your fetching anything more then a field or two, your far better off to
open a record set, fetch the record, and then have access to the entire
record.

Jim.



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________