Access 2007 - Left Join to a query returns #Error instead of Null -

similar question, problem query never solved:

#error showing in multiple left join statement access query when value should null

i #error when i'm expecting see null when doing left join there no corresponding record on right hand side of join:

chain               casespermonthperstore   monthoffirstorder naturally           2.3                     5/1/2011 tom's market        #error livingstons         #error everclear           3.1                     7/1/2012 bob's market        2.66                    5/1/2012 andy's exports      #error jamestowns          0.89                    7/1/2012 

it works fine if copy data table , left join table, assume there wrong in syntax of query:

select      monthrange.chain,     monthrange.casesshipped/iif(monthssincefirstorder.months>datediff("m",querydates.startdate,querydates.enddate)+1,                                 datediff("m",querydates.startdate,querydates.enddate)+1,                                 monthssincefirstorder.months)/numstores.numberofstores casespermonthperstore,     monthssincefirstorder.monthoffirstorder      querydates,      (         monthrange          inner join          numstores              on monthrange.chain=numstores.chain     )      inner join      monthssincefirstorder          on monthrange.chain=monthssincefirstorder.chain; 

this sql returns correct results, it's behaviour when left joining returning #errors.

nb strange iif statement in middle checks see if number of months since first order greater number of months included in specified date range - if date range has 6 months , first order 9 months before end date, uses 6; if first order 4 months before end date, uses 4.

-- edit update --

right, took elements of query out 1 one, , simplest can whilst still recreating left joining error:

select      monthrange.chain,      datediff("m",querydates.startdate,querydates.enddate)+1 casespermonthperstore      querydates,      monthrange; 

and how i'm left joining it:

select      chains.chain,      errorquery.casespermonthperstore      chains      left join      errorquery          on chains.chain=errorquery.chain; 

does in sql wrong?

while query should return null based on join type, allen browne states in article, bug: outer join expressions retrieved wrongly,

"instead, behaves if [the jet query optimizer] evaluating expression after has returned results lower-level query."

consequently, must select calculated field using expression evaluate null if right-side of join doesn't match left-side.

using pared-down code example:

select  month.chain, datediff("m",querydates.startdate,querydates.enddate)+1 casespermonthperstore querydates, monthrange;  select chains.chain, iif(isnull(errorquery.chain),null,errorquery.casespermonthperstore) chains left join errorquery on chains.chain=errorquery.chain;