[AccessD] Whilst on DLOOKUPs....

Hi Stuart

That's a nice tip! Never thought of that.

/gustav


>>> stuart at lexacorp.com.pg 11-05-2011 00:03 >>>
And combining that technique with Split() to get the fields into an array for further use can 
make it even more useful.

-- 
Stuart

On 10 May 2011 at 15:54, Gustav Brock wrote:

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