Dlookup in a query ?

[mvdrumaboy]

Is it possible to use the dlookup function in a query to create a
derived field (ie lookup a piece of information out of a table and
place it in the current record depending on the value of another field
in the same record) ?

I have a field named ProductID and want to create a Price field that
looks up the particular Price of the Product from the table
tblProducts. Is that possible ?

Your help is appreciated. Thanks.

 

[John Vinson]

Is it possible to use the dlookup function in a query to create a
derived field (ie lookup a piece of information out of a table and
place it in the current record depending on the value of another field
in the same record) ?

I have a field named ProductID and want to create a Price field that
looks up the particular Price of the Product from the table
tblProducts. Is that possible ?

Yes, just exactly as you described.

Put

Price: DLookUp("[Price]","[Products]","[ProductID] = " & [ProductID])

in a vacant Field cell in the query.

Or - especially if it's for a Report - why not simply create a query
joining this table to the Product table by ProductID? Or, for a Form,
you can use a Combo Box bound to the ProductID field, and display the
price in it; or include the price along with other fields from the
product table in the Combo's Rowsource and put a textbox on the form

=comboboxname.Column(n)

where n is the zero-based position of the price field in the query.


John W. Vinson[MVP]