Look up value from a table

[BAPERS]

I have a table called PlanFeeSchedule which contains 3 fields:

PlanID
FeeScheduleID
FeeScheduleEffectiveDate

I am entering a plan Id and a date in a form and want to look up the fee
schedule that is effective based on this date entered.

Sample data:
PlanID FeeScheduleID FeeScheduleEffectiveDate
1000 2006AB 1/1/2006
1000 2007AB 6/30/2007
1000 2009AB 1/1/2009

If I enter a planID of 1000 and a date of 12/31/07, I want to return the
FeeScheduleID 2007AB (I want to look up the effective date of the schedule
that is less than or equal to the date entered and return the corresponding
fee schedule).

I am new to Access and not sure what is the best method to accomplish this.
Any suggestions would be appreciated.

Thanks.

 

[Allen Browne]

DLookup() won't handle this, because you can't tell it which record to
return. You need the first match for the plan and date, sorted by
FeeScheduleEffectiveDate.

Here's a replacement called ELookup() that can do this:
http://allenbrowne.com/ser-42.html
It takes an extra argument that lets you specify the sort order, and so
returns the right one. For the example you gave, you would call it like
this:
=ELookup("FeeScheduleID", "PlanFeeSchedule",
"(PlanID = 1000) AND (FeeScheduleEffectiveDate >= #12/31/07#",
"FeeScheduleEffectiveDate")

If you will be doing this lots, it would probably be worth creating a query
that gives you the date ranges. This takes a bit of work to set up, but
would certainly be worthwhile if this is something you do many times in your
application. Tom Ellision explains how to do that here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html