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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ralph Wischnewski" <(E-Mail Removed)> wrote in
message news:216DED0C-DA54-4962-AB44-(E-Mail Removed)...
>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.