Judging by your naming convention, the error is likely to be in [Union SELECT ID] ?
Also it helps if you write the SQL in some way that it can be read a lot easier?
Code:
SELECT [Union SELECT ID].ID,
[Union SELECT ID].Co,
[Union SELECT ID].RHH_CUST_NUMBER,
[Union SELECT ID].IHH_ORDER_NUMBER,
[Union SELECT ID].RHH_INV_NUMBER,
[Union SELECT ID].RHH_RETURN_NUMBER,
[Union SELECT ID].RHH_NET_AMT,
[Union SELECT ID].IHH_NET_AMT,
[Union SELECT ID].RHH_SHIPPING_AMT,
[Union SELECT ID].IHH_SHIPPING_AMT,
[Union SELECT ID].RHH_TAX_AMT,
[Union SELECT ID].IHH_TAX_AMT,
[Union SELECT ID].R_OTHER_AMT,
[Union SELECT ID].RHH_MISC_AMT,
[Union SELECT ID].IHH_MISC_AMT,
[Union SELECT ID].RHH_TOT,
[Union SELECT ID].IHH_TOTAL_AMT,
[Union SELECT ID].compare AS type,
[Union SELECT ID].IHH_TAX_STATE,
[Union SELECT ID].Recovery
FROM ([Union SELECT ID]
LEFT JOIN [Canadian provinces]
ON [Union SELECT ID].IHH_TAX_STATE=[Canadian provinces].[Province code])
LEFT JOIN [Tax adjustments Credit Code 207]
ON ([Union SELECT ID].Co=[Tax adjustments Credit Code 207].Company_Num)
AND ([Union SELECT ID].RHH_RETURN_NUMBER=[Tax adjustments Credit Code 207].RHH_RETURN_NUMBER)
AND ([Union SELECT ID].RHH_INV_NUMBER=[Tax adjustments Credit Code 207].RHH_INV_NUMBER)
WHERE ((([Tax adjustments Credit Code 207].Company_Num) Is Null)
AND (([Tax adjustments Credit Code 207].RHH_RETURN_NUMBER) Is Null)
AND (([Tax adjustments Credit Code 207].RHH_INV_NUMBER) Is Null)
AND (([Canadian Provinces].[Province code]) Is Null))
ORDER BY [Union SELECT ID].ID;