NOT IN (Select) Query problems (1 Viewer)

[TimW]

Hi All
After spending the day looking for solutions i think i need to ask for help.
I have a form that has its recordset generated from SQL. All fine, this form is the available selections to automatically populate another from ( One is generated by the engineer the other by the production manager, basically when an engineer has logged a report the production manager can copy the data into the production managers form. The PM form was there first..so as to avoid a complete rewrite....) All good
Until I was requested to remove entries from this form after it was entered in the PM form. This was successful as long as I already had data in the PM report... to cut to the chase..

SELECT  
[INDENT]tblIncident.IncidentNo, 
tblIncident.Cell, 
tblIncident.IncidentTime, 
[EndTime]+[EndDate] AS ENDTD, 
tblIncident.AssetType, 
tblIncident.CellStopped, 
tblIncident.Issue,
tblIncidentSupport.Cause, 
tblIncidentSupport.Resolution, 
51835 AS ID, 
#12/03/2013 08:00:00# AS ShiftStart, 
#12/03/2013 16:00:00# AS ShiftFinish[/INDENT] 
FROM tblIncident LEFT JOIN tblIncidentSupport ON tblIncident.IncidentNo = tblIncidentSupport.IncidentNo 
WHERE 
[INDENT]((tblIncident.Cell)='1') 
AND ( ((tblIncident.IncidentTime) >= #12/03/2013 08:00:00# 
And (tblIncident.IncidentTime)< #12/03/2013 16:00:00#) 
OR ((tblIncident.IncidentTime)<#12/03/2013 16:00:00#)
AND (([EndTime]+[EndDate]) Is Null) 
OR (([EndTime]+[EndDate]) > #12/03/2013 08:00:00# 
And ([EndTime]+[EndDate]) <= #12/03/2013 16:00:00#)  [/INDENT])

The part above works for generating the required records if i did not want to get rid of the selected record. The next bit works if the select statement has something to return...

 AND tblIncident.IncidentNo NOT IN 
(SELECT  tblDowntime.IncidentNo FROM tblProductionTimes 
LEFT JOIN tblDowntime ON 
tblProductionTimes.ID = tblDowntime.ProductionTimesReference 
WHERE (tblProductionTimes.ID= 51835 ));

However, if there is nothing in the downtime table (as there will not be until something is entered) then the SQL will not work.
I generate the SQL dynamically using VBA from another form that has the dates in. So in theory I could change the recordsource dynamically, but I dont really want to introduce more complexity..
So the question:
How do I handle the no records returned in the query?
I have tried {wrapping} the query with NZ (I believe that will only work with records, which I dont have)
BTW in the downtime table other records that do not have the engineers report I have given a 0 (zero) (instead of leaving as a null)

Thanks in advance, if only for reading this far

T

 

[Geotch]

I think this is the problem: WHERE (tblProductionTimes.ID= 51835 )

If you take out that criteria does it work?

 

[CJ_London]

It's failing because if there is no record to return, it returns null, so wrap the subquery in the nz function

 

[TimW]

Thanks for the replys. Its not the WHERE part its the fact no results are returned.
As stated originally the NZ function does not work in this instance.

I have developed a work around for this where I only use the above query as the record source of the form after I know there is a value to return. If I don't know then I use the same query but without the AND NOT IN (SELECT....)

Alternately I could have tested for null before hand... I may have to do this if the users are not happy with my current soluton