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

Yeah, I did something like that as a temp solution, just to my client
can keep working.

This is damned weird.  I can do the same thing on any other two
tables and it works fine.

Is there a limitation on how many records can be returned in a
subquery?

-Christopher-

---- 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 10:57:12 +0930

>I was about to say that the AccessSQL dialect may not be able to 
>handle
>derived tables, but I tested it and it worked fine for me.
>
>Are they EntryID fields in both tables different data types perhaps?
>
>A workaround would be to use a Left join and filter where the 
>EntryID is
>Null:
>
>Select 
>    [field list]
>>From 
>    TimeEntry
>Left Join
>    EntryID
>ON 
>    TimeEntry.EntryID = LineItem.EntryID
>WHERE
>    LineItem.EntryID IS Null
>
>Cheers,
>Andrew
>
>-----Original Message-----
>From: Christopher Hawkins [mailto:clh at christopherhawkins.com] 
>Sent: Wednesday, 21 April 2004 10:30 AM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Query fails when using NOT IN ???
>
>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.
>
>Any idea?
>
>-C-
>
>
>-- 
>_______________________________________________
>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
>