Price change, find correct price for date

[Bill]

Hi All,
Hope this is the correct ng. Running Access 2000.
I am trying to get some assistance with how to handle rate
increases in a billing db. I have read google for a week and
I can't find anything to give me what I think I need. I would like
the past invoices to remain intact, after a rate increase. I
would like to use a rate table opposed to storing the rates
with each entry. I think I have structured the tables correctly to
achieve retrieving the rate based on a specific month date.
I have a report working except for retrieving the rate for the visit
when the rate has changed. The RateAmt is a part of the query.
Currently, if I add a rate change, I get entries for all rates.
I know I can store the RateAmt with the entry in tblmonthlyvisits,
but that doesn't meet the need.

I need help with the method and code to get the rate from the
tblCoRates based on the monthdate and other criteria.

Find the RateAmt
where maximum EffectiveDate is <= monthdate
where RateTypeID = RateTypeID
where CoID = CoID
My table structure is as follows:

tblCoRates
CoID (pk)
RateAmt (pk)
EffectiveDate (pk) always 1st of month
RateTypeID (fk)

tblRateType
RateTypeID
RateType

tblCo
CoID
CoName

tblTherapists
TherID
FName
LName

tblMonthlyVisits
TherID
MonthDate
CoID
RateTypeID

Thanks for your help.
Bill