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

by

While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly,

“Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query.”

Consequently, you must select the calculated field using an expression that will evaluate to Null if the right-side of the join doesn’t match the left-side.

Using your pared-down code as an example:

SELECT 
Month.Chain,
DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1 AS CasesPerMonthPerStore
FROM
QueryDates,
MonthRange;

SELECT
Chains.Chain,
IIf(IsNull(ErrorQuery.Chain),Null,ErrorQuery.CasesPerMonthPerStore)
FROM
Chains
LEFT JOIN
ErrorQuery
ON Chains.Chain=ErrorQuery.Chain;

More Related Contents:

  1. Insert SQL command with Datetime in MS-Access
  2. Save password for ODBC connection to MS SQL server from MS Access 2007
  3. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  4. Trouble with InputBoxes
  5. What are the rules governing usage of parenthesis in VBA function calls?
  6. Autonumber value of last inserted row – MS Access / VBA
  7. How to get random record from MS Access database
  8. create form to add records in multiple tables
  9. Operation must use an updatable query. (Error 3073) Microsoft Access
  10. How do I force VBA/Access to require variables to be defined?
  11. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  12. Is it better to use ADO or DAO in Access 2007?
  13. How to restart counting from 1 after erasing table in MS Access?
  14. Equivalent cURL in VBA?
  15. VBA procedure to import csv file into access
  16. How to select top 10 in Access query?
  17. ConcatRelated() in Access, using two keys
  18. Concatenating multiple rows into single line in MS Access [duplicate]
  19. Transform multiple rows into columns with unique key
  20. Different LIKE behaviour between my application and the Access query wizard
  21. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  22. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  23. Null values for variables in VBA
  24. MS Access query returning Chinese characters – possible table corruption?
  25. Using a centralized VBA module in multiple Access databases
  26. How to execute a query in ms-access in VBA code?
  27. How to make a passthrough / passthru query editable?
  28. How to add a menu item to the default right click context menu
  29. Force all users to disconnect from 2010 Access backend database
  30. Check if access table exists

Leave a Comment