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

David McAfee (Home) dmcafee at pacbell.net
Tue Apr 20 23:19:20 CDT 2004


This is a known issue with "IN (Select..."

You need to create an unmatched query using left join:

Select * from tblA
LEFT JOIN tblB
  ON tblA.PK = tblB.FK
WHERE tblB.FK = Null



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
Hawkins
Sent: Tuesday, April 20, 2004 6:48 PM
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.



More information about the AccessD mailing list