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??
Who is Participating?
Rey Obrero (Capricorn1)Commented:
test this

UPDATE [MaintenanceCosts] INNER JOIN RoadsIndex ON ([MaintenanceCosts].RoadID = RoadsIndex.RoadID)  
SET [MaintenanceCosts].PavementType = [RoadsIndex]![PavementType]
Where [MaintenanceCosts].Start >= RoadsIndex.[Start] And [MaintenanceCosts].Start <= RoadsIndex.[End]
kerikeriAuthor Commented:
Absolutely perfect.  Thank you for your quick response.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.