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

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.