If you could add another column that show the End Date of each range, it would be much easier.
So, for the records you posted above, the End Date would be:
2/4/2017
3/4/2017
4/1/2017
4/29/2017
6/3/2017
7/1/2017
7/19/2017
Then, you could do a query where your Fuel Date from this other table is between these two dates.
So, there would be no Join between the two tables, but the Criteria under your Fuel Date field would look something like:
Code:
>=[tblFuel_Rates]![EFFECTIVE_DATE] And tblFuel_Dates.FUEL_DATE)=[tblFuel_Rates]![END_DATE]
There are various ways you can create/populate this End Date field. You could use a loop through a RecordSet in Access VBA.
Or you could simply export to Excel, subtract one from the Effective Date below it, and re-import.