returning current information - going round in circles

[LindaBrewis]

Hi,

I'm quite new to VBA and am trying to write code for the following query.

I need it to look at the "sales date" in the "sales table", compare this to
"effective dates" in the "contracts table" and return the "contract
percentage" for the contract that is in effect at the "sales date". I will
usually have more than one contract for each product as contract percentages
may only be effective for the first 3 months of sale, and then a new rate
becomes effective.

I have looked at findfirst, Dlookup and If functions but cannot decide which
if any are the correct ones to use.

Thanks

Linda

 

[Ken Snell [MVP]]

Create a query that returns the contract number, effective date, and
contract percentage fields from the contracts table; order the results by
contract number (ascending) and effective date (descending). Save this
query.

SELECT ContractNumber, EffectiveDate, ContractPercentage
FROM Contracts
ORDER BY ContractNumber, EffectiveDate DESC;


Then use the saved query as the source of a DLookup function, and use the
contract number as a criterion and use the current date greater than or
equal to the effective date as a criterion.

DLookup("ContractPercentage", "NameOfSavedQuery", "ContractNumber="
& [ContractNumber] & " And EffectiveDate<=" & Date())