Access equivalent to Excel's VLookup function

Tom & John,

Thanks very much for your help on this matter.

Tom, I am taking your advice on learning SQL. I tried your SQL construct
and it works! I simplified the problem, however. Both tables are linked to
another table. For example, the price table is actually Price TableA with
fields of ItemID, Attribute #1, Attibrute #2, etc. and links with Price
TableB with fields of ItemID (the link), Date of Price Change, and Price.
That is, looking at the Price TableA with Price TableB as a subtable shows
the price history for each item. How does this affect the SQL statement you
sent me? Hope it doesn't complicate it too much.

John, I will try your lookup expression and compare the results.

Again, thanks to both of you for your help.

John

"John Vinson" wrote:

On Sat, 11 Mar 2006 20:54:24 -0800, john431
wrote:

Thank you for your reply, John.

I tried the DMax criteria for the Price field, which is the value I am
looking for. I used =DMax("[Price]", "[Price Table]", "[ItemID]="&[ItemID]).
This gave me the maximum value of the price for all cases of the Purchase
Date. For example, if the price table for a product is:


To get the most recent price using domain functions, try

=DLookUp("[Price]", "[Price Table]", "[ItemID] = " & [ItemID] & " AND
[Date Of Price Change] = #" & DMax("[Date Of Price Change]", "[Price
Table]", "[ItemID] = " & [ItemID]))

Or... much more efficient - use a Query. Just because VLookUp is the
typical solution in Excel doesn't mean that there is a "VLookUp
Equivalent" in Access. Excel is a spreadsheet; Access is a relational
database! They have different structures, different techniques, and
require different thinking.

John W. Vinson[MVP]