VBA required to run an Update command

I need to update the field PavementType in File 1 (MaintenanceCosts) based on the field Start being between Start and End in File 2 (RoadsIndex).  
I've written an update query and generated this SQL statement which works fine when the value is an exact match, i.e. MaintenanceCosts.Start = RoadsIndex.Start:

UPDATE [MaintenanceCosts] INNER JOIN RoadsIndex ON ([MaintenanceCosts].RoadID = RoadsIndex.RoadID) AND ([MaintenanceCosts].Start = RoadsIndex.[Start]) SET [MaintenanceCosts].PavementType = [RoadsIndex]![PavementType];

However, I think I need some VBA code to be able to compare MaintenanceCosts.Start  to be BETWEEN RoadsIndex.Start and RoadsIndex.End.  

Hopefully this is clear??