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

On 20 Apr 2004 at 19:48, Christopher Hawkins wrote:

> 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.
> 

1.  Does it resolve to:
" (NOT IN.... ) AND...." or "NOT (IN.... AND....)"
Try changing it to
"(NOT IN (SELECT BlockID FROM TG_InvoiceLine)) AND"


2. Are you 105% certain that it's not 
"(((TG_qryTimeBlockReview.[Date]) Between #" &
StartDate & "# And #" & EndDate & "#))" that's failing.

Parsing dates properly can be a real hassle. Your version is likely 
to fail if the default date fromat is "dd/mm/yy"

Try something like
....."Between " & Format$(StartDate,"#mm/dd/yy#") " & " AND " _
& Format$(EndDate,"#mm/dd/yy#")  & .....

> 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 & "#));"
> 
 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.