No Query results when one field is Null 1

[FoxProProgrammer]

Hi,

Here's my Query.

SELECT DISTINCT tbl_Jobs.JobID, tbl_Jobs.JobNum AS [Job #], tbl_Jobs.Date_Completed AS [Date Completed], tbl_LRU.LRU_Name AS [LRU Type], tbl_Jobs.LRU_SN AS [LRU Serial #], IIf([tbl_Jobs].[Customer]=1,"PBL","Non-PBL") AS Customer, tbl_aircraft.aircraftName AS Aircraft, IIf([tbl_Jobs.NFF]=Yes,"?","") AS NFF
FROM tbl_LRU INNER JOIN (tbl_Jobs INNER JOIN tbl_aircraft ON tbl_Jobs.AircraftID = tbl_aircraft.aircraftID) ON tbl_LRU.LRUID = tbl_Jobs.LRU_Repair
WHERE (((tbl_Jobs.JobNum)=[cbo_viewBy].[Value]));

The Query returns no records when tbl_jobs.aircraftID is Null. The aircraftID is not needed for certain records - that's why it is null. I tried using the Nz function in the Query:

Nz(tbl_aircraft.aircraftName,0) AS Aircraft

That doesn't work either. I presume that no records are returned because of the Join condition ON tbl_Jobs.AircraftID = tbl_aircraft.aircraftID

Access can't match a null? Is there a way around this without defining what would be phantom data in my tables? In other words, I could create an aircraftID that represents Null, but this seems silly when I really don't care if that field is null in some cases.

Thank you!

dz

 

[Lilliabeth]

In an inner join, only the records that have a matching value in the join field of both tables are displayed. ("Orphan records" - records that are in the parent table but not the child table are ignored in an inner join.)

Have a look at LEFT JOIN


Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[FoxProProgrammer]

Thank you so much, Lilliabeth! The LEFT JOIN works.

Best regards,


dz