Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    22

    Unanswered: Error 3071 when running a report

    Hi All,

    I have a database which usually runs without issue on the network at my work. Recently a couple of users have updated to Office 2007 from 2003. Now, when running this particular report it fires the above error. The report still runs on other machines running Access 2003 with no error.

    The SQL statement is as follows:
    Code:
    SELECT ITEM.MORP, ITEM.ITEMCODE, ITEM.DESCRIPT, ITEM.QTYONHAND, ITEM.ADFIELD1, ITEM.LOCCLASS, ITEM.CATEGORY, ITEM.ROQ, qryShippingItem3MonthQty.[3MonthCases], Forecast.[3MthFcst], qryEncoreProduction3MthQty.SumOfCaseMade AS 3MthMade, IIf([3MthFcst]=0,0,[QTYONHAND]/[3MthFcst]*12) AS WCoverFcst
    FROM ((ITEM LEFT JOIN qryShippingItem3MonthQty ON ITEM.ITEMCODE = qryShippingItem3MonthQty.REFID) LEFT JOIN qryEncoreProduction3MthQty ON ITEM.ITEMCODE = qryEncoreProduction3MthQty.ITEMCODE) LEFT JOIN Forecast ON ITEM.ITEMCODE = Forecast.ITEMCODE
    GROUP BY ITEM.MORP, ITEM.ITEMCODE, ITEM.DESCRIPT, ITEM.QTYONHAND, ITEM.ADFIELD1, ITEM.LOCCLASS, ITEM.CATEGORY, ITEM.ROQ, qryShippingItem3MonthQty.[3MonthCases], Forecast.[3MthFcst], qryEncoreProduction3MthQty.SumOfCaseMade, IIf([3MthFcst]=0,0,[QTYONHAND]/[3MthFcst]*12)
    HAVING (((ITEM.LOCCLASS)="A" Or (ITEM.LOCCLASS)="B" Or (ITEM.LOCCLASS)="C" Or (ITEM.LOCCLASS)="OS") AND ((ITEM.CATEGORY) Like "F%"))
    ORDER BY ITEM.MORP, ITEM.ITEMCODE;
    Any help would be greatly appreciated.
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno why its doing that may well be a difference in the underlying JET engine, or possibly a change in default library

    your SQL looks fine
    arguably you could consider refining the having clause
    HAVING ITEM.LOCCLASS in ("A","B","C","OS") AND ITEM.CATEGORY Like "F%"

    short of removing chunks of code int he SQL till you find which element causes grief I don't think I can help
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    22
    Thanks for the reply healdem.

    That would imply that the error is firing because of something other than the SQL statement? There are a number of calculated fields on the report. Could this be the reason?
    I tried following the advice here Microsoft Access Tips - Avoid #Error in form/report with no records in some calculated fields.

    Interesting point about the default library, I checked the references and the only difference is the Object Library going from 11.0 to 12.0.

    Thanks again for your help.
    Dan

  4. #4
    Join Date
    Mar 2010
    Posts
    22

    [solved]

    I managed to spend some time on this today. It appears that one of the calculated fields was in fact causing this error. There was a field which was dividing by a returned null value.

    The thing that is disappointing about this is that Access wasn't giving any guidance as to which field was causing the error. It should tell you.

Posting Permissions

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