[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
>