Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Cleveland USA

    Question Unanswered: Invalid Operation error message with Select Query

    I am using Access 2002 with SQL Server 2000 backend.

    I am working on a query that links a table to two queries. When I use just one of the queries, it works. When I add the second query, I get an "Invalid Operation" error message. It doesn't matter which query I add first, as long as I just use one it runs. Any ideas?

    Here is the SQL for the query I'm trying to create:

    SELECT Right([Table1].[Field1],2) AS td, Table1.FIELD1, Sum(qryPaymentHistory_All.DueTYPE1_SUB1) AS SumOfDueTYPE1_SUB1, Sum(qryPaymentHistory_All.DueTYPE2_SUB1) AS SumOfDueTYPE2_SUB1, Sum(qryPaymentHistory_All.DueTYPE1_SUB2) AS SumOfDueTYPE1_SUB2, Sum(qryPaymentHistory_All.DueTYPE2_SUB2) AS SumOfDueTYPE2_SUB2, IIf([TYPE2orTYPE1]="TYPE2","TYPE2","TYPE1-Blend") AS Type, Table1.Status, qryPaymentHistory_All.FIELD1, qryCaseTypeBreakdown_ReturnErrorMessage.FIELD1
    FROM (Table1 LEFT JOIN qryPaymentHistory_All ON Table1.FIELD1 = qryPaymentHistory_All.FIELD1) LEFT JOIN qryCaseTypeBreakdown_ReturnErrorMessage ON Table1.FIELD1 = qryCaseTypeBreakdown_ReturnErrorMessage.FIELD1
    WHERE (((Table1.AddedDate)<#8/23/2006#)) OR (((Table1.AddedDate)<#8/23/2006#)) OR (((Table1.AddedDate)<#8/23/2006#))
    GROUP BY Right([Table1].[Field1],2), Table1.FIELD1, IIf([TYPE2orTYPE1]="TYPE2","TYPE2","TYPE1-Blend"), Table1.Status, qryPaymentHistory_All.FIELD1, qryCaseTypeBreakdown_ReturnErrorMessage.FIELD1
    HAVING (((Table1.Status)="paid" Or (Table1.Status)="no pay due" Or (Table1.Status)="not eligible" Or (Table1.Status)="Special " Or (Table1.Status) Like "debt*")) OR (((qryPaymentHistory_All.FIELD1) Is Not Null)) OR (((qryCaseTypeBreakdown_ReturnErrorMessage.FIELD1) Is Not Null))
    ORDER BY Table1.FIELD1;

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    Is it me, or is that SQL hard to read?

    Have you considered indenting it (like you do with your VBA code... which you do, right.?) and posting it back up here?

    I'll be damned if I'm going to debug a block of text like that
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    lisa, no offence, but have you given any serious thought to what you're actually doing?

    what is the purpose of this complex expression --

    WHERE (((Table1.AddedDate)<#8/23/2006#)) OR (((Table1.AddedDate)<#8/23/2006#)) OR (((Table1.AddedDate)<#8/23/2006#))

    i hope this isn't symptomatic of the entire query... | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts