Access - Left Join returns #Error instead of Null -

I have already asked a similar question, but I now have table / queries with an example database (hopefully) Descriptive naming:

The query takes precedence from queries.

But when I run the query LeftJoinReturnsError, instead of returning the expected return of the #Error to three additional chains in the chain table, instead of returning.

If I change QueryDates, everything works well from a query to the table, so it seems that the problem is where, but I have an IIF (IsNull, Null, 0) condition to solve it Can not even use it.

An additional 50 representative tells those who solve it, as long as I can work to overcome them. :)

(Previous questions if you are interested :)

- Update -

Output will look something like this, though I do not test put the database remember the exact data:

  Chen Mamlonpratibnditportstor farm store 2:33 agricultural exports 2B pencil 3.6 Bob's market   
 in the original, Chain table in any series which is not in other tables, it should return the tap as part of the left joining New.   
 
 
 excellent post on the bug I had not seen it, but it is quite clear that what's actually happening in is. The only way I have extended to some level expression query upwards does not support APPLY section of it could to deal with me because entry into the lateral or SQL Server. The following gives you the output you want:  
  Select Chains.Chain, (Select ChainsCasesPerMonthPerStorePreviousMonthRange.CasesShipped / (Datefree ("m" ChainsCasesPerMonthPerStorePreviousMonthRange.StartDatePrevious, ChainsCasesPerMonthPerstreamMonthRange.EndDatePrevious) +1) / Chen ChainsCasesPerMonthPerStorePreviousMonthRange .NumberOfStores AS Expr1 Kasijhprintpoyrstorprarwotthanmonthreenj where Chen Kasijhprintmonthperstor Preewartmonthrenjkchai = Chen. Chen) Chen Aksprod 2;   
 This is very ugly, but it works. Certainly in SQL Server or DB, you will not need to be included in the side because the process is linked with the external expression.