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??