Return Value if Not in Criteria

[papic1972]

I have a query that I want to use to display a list of our customer's jobs that have not ordered any product between a date range for e.g between 30/11/04 to 22/02/05. I require the query to return customer names & jobs if NO value of tblOrderInput.OrderEntryDate is found between the designated date range.

My sql statement from my query looks like this so far:

SELECT DISTINCT tblJob.CompletedJob, tblJob.Customer, tblJob.StreetAddress, tblJob.Suburb, tblJob.JobId, tblOrderInput.OrderEntryDate
FROM tblJob INNER JOIN tblOrderInput ON tblJob.JobId = tblOrderInput.JobNo
WHERE (((tblJob.CompletedJob)=False));


I don't know how to incorporate the date range bit into sql. Any help would be appreciated.

Thanks in advance.

 

[PHV]

Something like this ?
SELECT CompletedJob, Customer, StreetAddress, Suburb, JobId
FROM tblJob
WHERE CompletedJob=False
AND JobId Not In (SELECT DISTINCT JobId FROM tblOrderInput WHERE OrderEntryDate BETWEEN #2004-11-30# And #2005-02-22#);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244