I have a table that has a date range and a percentage called Underlying_Percentage. My second table has multiple cost fields and an effective date called MAB_report. I need to get the percentage from table Underlying_Percentage based on the effective_date in table MAB_report. If I can get the percentage to pull over I can get the multiplication part. I was thinking of a Dlookup but I need to make sure that if the effective date is between the range the correct percentage pulls.
Underlying_Percentage Table
Date_Range Percentage_Amount
01/01/2015 .03%
05/01/2016 .08%
01/01/2017 .05%
MAB_report Table
Effective_Date Underlying_Percentage Premium_Paid Total_Underlying_Premium
02/10/2015 .03% $100.00 =$103
05/10/2017 .05% $100.00 =$105
Thanks
Chad