Any M$ Access Programmers? Familiar with DLookup?

[Hawkeye-X]

I'm trying to figure out how exactly the function would work for us...

Hawkeye-X

[orthogonal]

1. Google is my friend; I apparently am yours

I've never heard of this (I'm not a Access programmer), but a quick googling explains that DLookup is apparently shorthand for a query.

It takes three arguments, a column ("field") name, a table name, and a where clause.

If the sql select were
select foo from bar where baz = 1

the corresponding DLookup would be
DLookup( "foo", "bar", "baz = 1" )


The field name may be a computed field, e.g., for this sql:
select firstname + ' ' + lastname from bar where baz = 1

the corresponding Dlookup would be
DLookup( "firstname + ' ' + lastname", "bar", "baz = 1" )

The main use seems to be as a correlated subquery in forms.

In sql, if we have an employee table with employee data including a department number to which the employee belongs, and a department table with department names, we might use a correlated subquery to select employee and department names (it would of course be simpler, and would run faster, just to use a join in this, but not in all, cases).

It's call a "correlated" subquery because the subquery's where clause is dependent on the main query's result. So for each row in the main query, a separate subquery is done (and that's why it's often slower). The sql would be:
select a.empname,
(select b.deptname from department b where b.deptid = a.deptid)
from employee a where ....

Within a form, you can use DLookup to do this
DLookup("deptname", "dept", "deptid = &{deptid})

(The curly brackets are incorrect, they should be square brackets, but I don't know how to display those in this forum.)

Then, for each row returned on the form from employee, the department name from department would be looked up.

However, if possible just do a join in the SQL; this will likely be faster, and more portable if you move from access to another grand of database. DLookup is not portable.