Thank you swuehl.
We had thought that was the cause but hadn't thought to use the year function.
We do have pretty much the same issue with trying to extract month number from first load from date field. Second table has month as single digit for months 1 to 9. I've looked on help files and have only come around month(date) function but that only gives you returns a text string representing the month. We getting similar incorrect error as we were getting on year. So same issue.
What would suggest for similar case with month number? Colleague was thinking using MID with ROUND combination? Not tried mind you, just an idea.
month() function has also a text and a numerical representation. Check out
If you have a complete date in the first table and year, month, day in the second, it might be better to format both as dates, i.e. in the second table, you could create a Date using
makedate(TargetsTblYear ,TargetsTblMonth, TargetsTblDay) as DateField,