Susan Harkins
ssharkins at bellsouth.net
Tue Apr 20 21:17:25 CDT 2004
Are you sure the data you're checking meets the conditions? I didn't see the original question, but if the syntax is correct, then the data's wrong. I've seen something as simple as a leading space throw off the results and they're hard to find. Right. As long as that IN statement is there, this thing returns zero records. There should be 262. -C- ---- 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 11:30:53 +0930 >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. >-- >_______________________________________________ >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