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

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.