Join type makes query fail 1

[jazminecat23]

Hi all - I have a strange situation. I have a db that someone else built with a whole slew of queries in it. The user needed to add a text box to all of the reports (dozens) to indicate what period they cover. So she made a single record table where she inputs the period information into one simple text field. This table only has the one record.

Then she added that field to all the queries that her reports were based on. This way she only has to change this one thing, and it will update all of the reports at once.

And for the most part, it worked. Except in the queries where she has a left join. Then the query fails. Below are two samples - one that works (the added field is in red):

SELECT Tbl_Main.Fund, Tbl_Main.[Cost Center], Tbl_Main.Employee_Name, Nz([DBA_4300],0)-Nz([P4300],0) AS D4300, Nz([DBA_4303],0)-Nz([P4303],0) AS D4303, Nz([DBA_4304],0)-Nz([P4304],0) AS D4304, Nz([DBA_4325],0)-Nz([P4325],0) AS D4325, Nz([DBA_4405],0)-Nz([P4405],0) AS D4405, Nz([DBA_4450],0)-Nz([P4450],0) AS D4450, Nz([DBA_4465],0)-Nz([P4465],0) AS D4465, [red][Benefit Dates].Date
FROM [Benefit Dates][/red], [Qry_Compare Prev Month] INNER JOIN Tbl_Main ON [Qry_Compare Prev Month].SSN = Tbl_Main.SSN
WHERE (((Nz([DBA_4300],0)-Nz([P4300],0))<>0)) OR (((Nz([DBA_4303],0)-Nz([P4303],0))<>0)) OR (((Nz([DBA_4304],0)-Nz([P4304],0))<>0)) OR (((Nz([DBA_4325],0)-Nz([P4325],0))<>0)) OR (((Nz([DBA_4405],0)-Nz([P4405],0))<>0)) OR (((Nz([DBA_4450],0)-Nz([P4450],0))<>0)) OR (((Nz([DBA_4465],0)-Nz([P4465],0))<>0));

and one that doesn't work (the error says there is a problem with the joins)

SELECT Tbl_Main.Fund, Tbl_Main.SSN, Tbl_Main.Employee_Name, Qry_4720C.Account_Number, Qry_4720C.DBA_Number, Qry_4720C.DBA_4720, Qry_4725C.Account_Number, Qry_4725C.DBA_Number, Qry_4725C.DBA_4725, Tbl_Main.[Employee No], [red][Benefit Dates].Date
FROM [Benefit Dates][/red], (Tbl_Main LEFT JOIN Qry_4725C ON Tbl_Main.SSN = Qry_4725C.SSN) LEFT JOIN Qry_4720C ON Tbl_Main.SSN = Qry_4720C.SSN
WHERE (((Qry_4720C.DBA_4720)<>0)) OR (((Qry_4725C.DBA_4725)<>0));

Can anyone here help me fix the second one so that it will not throw an error and will just add that one field to her report? Thanks!!

 

[Remou]

Why not DlooUp the field in the report rather than add it to the query?

 

[jazminecat23]

I hadn't even thought of that! Great idea. So, since the Benefit Dates table only has the one record, can I just do something like DLookup("[Date]", "Benefit Dates", "Date = is not null")? I just need it to give me that one record...

Thanks!

 

[PHV]

=DLookup("[Date]", "Benefit Dates", "[Date] Is Not Null")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jazminecat23]

PHV you are my hero, once again. Thanks a million!

 

[dkillilea]

I am having the same problem as jaz. I'm linked to a View I created in SQL Server. Every time I do a left join with those Views in Access I will miss records from any column that can have nulls (like text fields) Any suggestions?

Thanks, Doug