DLookup Function in Queries

What does a DLookup Function "criteria" expression in a
query look like? I'm using Access 97 and the help files
state DLookup Functions can be done within the design of
the query but it doesn't provide an example. I'm able to
create a DLookup calculated control on a form, but without
an example to look at and study, I don't know where to
start...
Thank you for your help.

It would be similar in a query. It would be used in a calculated field in a
query. Usually, you can just include the other table in the query with the
appropriate links and get the answer you need, but there are times that the
DLookup is needed.

This may be a poor example, but should give you an idea of what it looks
like. In the Field box of the query design grid, try something like:

MyCalculatedField: DLookup("[Field1]", "[Table1]", "[Field2]=" &
[Table2].[Field2])

This would use the value of Table2.Field2 of the current record to provide
criteria for the DLookup statement. In this case though, it would probably
be better just to add Table1 to the query and link Table1.Field2 to
Table2.Field2 then add Table1.Field1 to the query's output. If you don't
want to limit the records from Table2, you could use an Outer Join to do
this. I suppose that one reason to do this, would be if you already had an
outer join in the query and adding this one would create an "ambiguous outer
join", this may be a work around.

--
Wayne Morgan
Microsoft Access MVP


"Jim" wrote in message
...
What does a DLookup Function "criteria" expression in a
query look like? I'm using Access 97 and the help files
state DLookup Functions can be done within the design of
the query but it doesn't provide an example. I'm able to
create a DLookup calculated control on a form, but without
an example to look at and study, I don't know where to
start...
Thank you for your help.