[AccessD] Query fails when using NOT IN ???

Right.  As long as that IN statement is there, this thing returns
zero records.  There should be 262.

-C-

---- Original Message ----
From: andrew.haslett at ilc.gov.au
To: accessd at databaseadvisors.com, 
Subject: RE: [AccessD] Query fails when using NOT IN ???
Date: Wed, 21 Apr 2004 11:30:53 +0930

>And it still doesn't work if you drop off the date related 'AND' 
>clause in
>the where function?
>
>-----Original Message-----
>From: Christopher Hawkins [mailto:clh at christopherhawkins.com] 
>Sent: Wednesday, 21 April 2004 11:18 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Query fails when using NOT IN ???
>
>Yes, same datatype.  No, no quotes.
>
>Here's the SQL statement.  Understand that StartDate and EndDate are
>dates that were passed in to the function that contains this SQL. 
>I've pulled it apart into it's component pieces; it's definitely the
>IN statement that is failing.
>
>Here's the SQL:
>
>        sInsert = "INSERT INTO TG_InvoiceLine (BlockID, UserId,
>InvoiceID, ItemID, [Date], " & _
>            "Description, QB_ActivitiesID, Billable, QBCustomerJob,
>Qty, LineTotal, LineCost, Rate) " & _
>            "SELECT TG_qryTimeBlockReview.BlockID,
>TG_qryTimeBlockReview.UserId, TG_qryTimeBlockReview.InvoiceId, " & _
>            "TG_qryTimeBlockReview.ItemID,
>TG_qryTimeBlockReview.[Date], TG_qryTimeBlockReview.BlockDescription,
>" & _
>            "TG_qryTimeBlockReview.QB_ActivitiesID,
>TG_qryTimeBlockReview.Billable, " & _
>            "TG_qryTimeBlockReview.QB_NameID,
>TG_qryTimeBlockReview.Duration AS Qty, " & _
>            "TG_qryTimeBlockReview.LineTotal,
>TG_qryTimeBlockReview.LineCost, TG_qryTimeBlockReview.TG_Rate " & _
>            "FROM TG_qryTimeBlockReview WHERE
>TG_qryTimeBlockReview.BlockID NOT IN (SELECT BlockID FROM
>TG_InvoiceLine) AND (((TG_qryTimeBlockReview.[Date]) Between #" &
>StartDate & "# And #" & EndDate & "#));"
>
>-C-
>
>---- Original Message ----
>From: stuart at lexacorp.com.pg
>To: accessd at databaseadvisors.com, 
>Subject: Re: [AccessD] Query fails when using NOT IN ???
>Date: Wed, 21 Apr 2004 11:19:14 +1000
>
>>On 20 Apr 2004 at 19:00, Christopher Hawkins wrote:
>>
>>> All,
>>> 
>>> This is weird.  I have a query that is supposed to return all 
>>records
>>> from a Time Entry table where the Entry ID is not in the Line Item
>>> table.
>>> 
>>> So basically, it's "SELECT [field list here] FROM TimeEntry WHERE
>>> EntryID NOT IN (SELECT EntryID FROM LineItem)".
>>> 
>>> Pretty simple, right?  I can verify that there are a ton of 
>records
>>> in TimeEntry that have no corresponding record in LineItem.  But 
>>when
>>> I run the query above, it returns no records.
>>> 
>>
>>Is EntryID the same datatype in both tables? 
>>
>>As another thought, make sure that you haven't "quoted" the 
>subquery 
>>in your actual code..
>>
>>Maybe pasting the actual code would help.
>>
>>
>>
>>
>>
>>
>> 
>>-- 
>>Lexacorp Ltd
>>http://www.lexacorp.com.pg
>>Information Technology Consultancy, Software Development,System 
>>Support.
>>
>>
>>
>>-- 
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>
>
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>IMPORTANT - PLEASE READ ******************** 
>This email and any files transmitted with it are confidential and 
>may 
>contain information protected by law from disclosure. 
>If you have received this message in error, please notify the sender 
>immediately and delete this email from your system. 
>No warranty is given that this email or files, if attached to this 
>email, are free from computer viruses or other defects. They 
>are provided on the basis the user assumes all responsibility for 
>loss, damage or consequence resulting directly or indirectly from 
>their use, whether caused by the negligence of the sender or not.
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>