Query not returning all records

[ZOR]

I have a problem with this query. It does not return records if any of the audio fields are not filled. Can someone please tell be how to convert it to allow nulls. Very greatfull.

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
FROM (Audio AS Audio_3 INNER JOIN (Audio AS Audio_2 INNER JOIN (Audio AS Audio_1 INNER JOIN (Audio INNER JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
ORDER BY TXCLIPS.Start;

 

[Remou]

It seems to me that you must have picked up this informstion somewhere in these fora by now. Perhaps you could post your suggestion for handling nulls?

 

[ZOR]

I tried this but it does not work.

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
FROM (Audio AS Audio_3 INNER JOIN (Audio AS Audio_2 INNER JOIN (Audio AS Audio_1 INNER JOIN (Audio INNER JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION])) OR (((Audio.AudioType) Is Null) AND ((Audio_1.AudioType) Is Null) AND ((Audio_2.AudioType) Is Null) AND ((Audio_3.AudioType) Is Null))
ORDER BY TXCLIPS.Start;

 

[ZOR]

Solved. It's not because of nulls, its because I was using an inner join.

 

[Remou]

Well done, keep it up.

 

[ZOR]

Thanks, Regards