Access equivalent to Excel "Vlookup"

[Ralph Wischnewski]

I am starting to work on a database that will calculate the escalations on
contract prices. For example, a contract my specify an aniversary date on
which the price can change by some kind of Price index. The anniversary date
will fall within a range of dates that the Index applies to. So, the
escalation date may be Jan 15 and the index value will fall within the range
of Jan 1 to March 30. This is not unlike applying a quantity discount.

I am imagining that I will have the index value and date range in one table.
The basic contract data will be in other tables.

I am having a hard time imagining how I will "lookup" the date range and
return the index form the "Index" table. I notice that Dlookup or subqueries
will not work. I have not been able to find an example of the code that will
help me here.

Can someone point me in the basic direction that will get me going.

 

[Allen Browne]

DLookup() is the starting point, but it lacks the ability to specify how to
order the records, so it may not work for you. Basics of DLookup():
http://allenbrowne.com/casu-07.html

There is an extented DLookup() replacement in this link:
http://allenbrowne.com/ser-42.html
The extra argument lets you specify how to order the records, and therefore
which one to retrieve.

If you want something more powerful still, see Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html