Why is Left Join not working?

[Sean O'Halloran]

Hello All,

My Goal: Have values which have NOT been entered by the user display on a report.

What I've Done: 1. Searched the forum for an hour and came up with Pat Hartman's advice to create a Left Join / IS Null query structure. 2. Created a table of never-changing values that need to appear on a report if they have not been selected by the user. 3. Built this query in Access 2003:

SELECT tblFinancial_Income_Dummy.ID, tblFinancial_Income_Dummy.CodeValue, tblFinancial_Income_Dummy.CodeText1, tblAPS_Income.CaseIDkey, tblAPS_Income.Income_Source
FROM tblFinancial_Income_Dummy LEFT JOIN tblAPS_Income ON tblFinancial_Income_Dummy.CodeValue = tblAPS_Income.Income_Source
WHERE (((tblAPS_Income.CaseIDkey)=23055) AND ((tblAPS_Income.Income_Source) Is Null));

Result: No results show. My test record - 23055 -contains 3 of 14 possible values, so I should have 11 dummy records resulting from my query.

Question: What am I doing wrong?

Sean

 

[redneckgeek]

WHERE (((tblAPS_Income.CaseIDkey)=23055)

Trumps the left join. You're filtering for a record on the right side of the left join.

 

[Sean O'Halloran]

Thank you! ...could you suggest any way around that issue?

By the way - I fell off my chair laughing at your motto: "Build a man a fire.."

 

[Sean O'Halloran]

Got it, I think. I created a query that filters for the 'tblAPS_Income.CaseIDkey' and made that the right-handed data source in the original query.

Thank you again RNG, you D+D!!

Smilin' Sean