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

Christopher

Should work indeed (does in A2k2)

a.1 If in Query designer, look at properties of query 
a.2 Make sure the both EntryID fields are of the same DataType
a.3 check for trailing spaces if this datatype is Text (can happen with
impoerts from other sources or connections with other sources. Use Trim() to
strip of spaces in the query (see below)

b. avoid possible ambiguity by ysing table

SELECT [field list here] 
FROM TimeEntry
WHERE Trim(TimeEntry.EntryID) NOT IN 
(SELECT Trim(LineItem.EntryID) FROM LineItem)

c. try the JOIN equivalent, works probably faster because the engine should
deliver better optimization

SELECT [field list here] 
FROM TimeEntry LEFT JOIN LineItem
ON TimeEntry.EntryID = LineItem.EntryID
WHERE (LineItem.EntryID  Is Null) ;

d. Try 

SELECT [field list here] 
FROM TimeEntry
WHERE NOT EXISTS 
(SELECT LineItem.EntryID 
FROM LineItem
WHERE LineItem.EntryID = TimeEntry.EntryID) ;

e. Give Up



--

>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>Christopher Hawkins
>Sent: Wednesday, April 21, 2004 2:00 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
>