Christopher Hawkins
clh at christopherhawkins.com
Tue Apr 20 21:27:14 CDT 2004
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUGH! There were some nulls in the BlockID field of the TG_InvoiceLine table. I set those to 0 and the IN statement started to work again. Heh. What an idiot. Thanks, everybody, and especially Susan for bringing up the dirty data angle. -C- ---- Original Message ---- From: ssharkins at bellsouth.net To: accessd at databaseadvisors.com, Subject: RE: [AccessD] Query fails when using NOT IN ??? Date: Tue, 20 Apr 2004 22:17:25 -0400 >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 > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >