Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Error (Access 2k)

    I recently decompiled and then recompiled a replicated database that contained a report that was tied to the following query:

    SELECT tblBCO.BCOZone, tblBCO.BCOState, tblLitData.LitCR, tblLitData.ClaimStatus, tblLitData.DefCounsel, [IMECosts]+[SIUCosts]+[ExpertCosts]+[AttorneyFees]+[OtherLegalFees] AS TotExpense, tblLitData.PreSuitOffer, tblLitData.SettlementAmount, IIf([tblLitData].[LitPhase_ID]=1,1,0) AS Phase1, IIf([tblLitData].[LitPhase_ID]=2,1,0) AS Phase2, IIf([tblLitData].[LitPhase_ID]=3,1,0) AS Phase3, IIf([tblLitData].[LitPhase_ID]=4,1,0) AS Phase4, IIf([tblLitData].[LitPhase_ID]=5,1,0) AS Phase5, IIf([tblLitData].[LitPhase_ID]=6,1,0) AS PhaseLast, IIf([tblLitData].[LitPhase_ID]=7,1,0) AS PhaseTrial, IIf([SettlementAmount]=0,0,([SettlementAmount]-[PreSuitOffer])/[PreSuitOffer]) AS PercInc
    FROM tblLevelWin RIGHT JOIN (tblInjury RIGHT JOIN (tblLitPhase RIGHT JOIN (tblSuitReasons INNER JOIN (tblVenue INNER JOIN (tblDefCounsel INNER JOIN (tblBCO INNER JOIN tblLitData ON tblBCO.BCO = tblLitData.BCO_ID) ON tblDefCounsel.DefCounsel = tblLitData.DefCounsel) ON tblVenue.Venue_ID = tblLitData.Venue_ID) ON tblSuitReasons.SuitReasons_ID = tblLitData.SuitReasons_ID) ON tblLitPhase.LitPhase_ID = tblLitData.LitPhase_ID) ON tblInjury.Injury_ID = tblLitData.Injury_ID) ON tblLevelWin.LevelWin_ID = tblLitData.LevelWin_ID
    WHERE (((tblLitData.ClaimStatus)="Closed") AND (([Forms]![frmReport]![cboRptZone]) Is Null Or ([Forms]![frmReport]![cboRptZone])=[tblBCO.BCOZone]) AND (([Forms]![frmReport]![cboRptState]) Is Null Or ([Forms]![frmReport]![cboRptState])=[tblBCO.BCOState]) AND (([Forms]![FrmReport]![cboBCO]) Is Null Or ([Forms]![FrmReport]![cboBCO])=[tblBCO].[BCO]) AND (([Forms]![frmReport]![cboPreSuit]) Is Null Or ([Forms]![frmReport]![cboPreSuit])=[tblLitData].[PreSuitCR]) AND (([Forms]![frmReport]![cboLitCR]) Is Null Or ([Forms]![frmReport]![cboLitCR])=[tblLitData].[LitCR]) AND (([Forms]![frmReport]![cboDef]) Is Null Or ([Forms]![frmReport]![cboDef])=[tblDefCounsel.DefCounsel]) AND (([Forms]![frmReport]![cboPlain]) Is Null Or ([Forms]![frmReport]![cboPlain])=[tblLitData].[PlainFirm]) AND (([Forms]![frmReport]![cboVenue]) Is Null Or ([Forms]![frmReport]![cboVenue])=[tblVenue].[Venue]) AND (([Forms]![frmReport]![cboReason]) Is Null Or ([Forms]![frmReport]![cboReason])=[tblSuitReasons].[SuitReasons]) AND (([Forms]![frmReport]![txtFrom]) Is Null Or ([Forms]![frmReport]![txtFrom])<=[tblLitData].[DateofSettlement]) AND (([Forms]![FrmReport]![txtTo]) Is Null Or ([Forms]![FrmReport]![txtTo])>=[tblLitData].[DateofSettlement]) AND (([Forms]![frmReport]![cboLitPhase]) Is Null Or ([Forms]![frmReport]![cboLitPhase])=[tblLitPhase].[LitPhase]) AND (([Forms]![frmReport]![cboInjury]) Is Null Or ([Forms]![frmReport]![cboInjury])=[tblInjury].[InjuryType]) AND (([Forms]![frmReport]![cboSettledBy]) Is Null Or ([Forms]![frmReport]![cboSettledBy])=[tblLitData].[SettledBy]) AND (([Forms]![frmReport]![cboLevelWin]) Is Null Or ([Forms]![frmReport]![cboLevelWin])=[tblLevelWin].[LevelWin]));


    The query still works fine by itself and worked with the report prior to the decompile/compile and then compact and repair. Now, however, I get the following error:

    "This expression is typed incorrectly, or it is too complex to be evaluted. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

    The query runs fine without any records, so I assume the complexity of the query is the problem. However there is not a problem just running the query. Is there a work around or way to solve this problem, or a way to simplify my query to get this to run?

    Any ideas are always appreciated. Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Error (Access 2k)

    Does it help if you replace

    IIf([tblLitData].[LitPhase_ID]=1,1,0) AS Phase1

    by

    -([tblLitData].[LitPhase_ID]=1) As Phase1

    and similarly for Phase2 ... Phase7?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Error (Access 2k)

    No, it did not make a difference. Query still works, but report will not work, giving the same message.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Error (Access 2k)

    I don't think I can add anyhthing without seeing the database, except remarking that you have an awful lot of form parameters. (And even then, I don't know if we could solve this.)

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Error (Access 2k)

    I located the problem. Thanks for taking the time to look at it though.

    It appears I had a couple of calculations on the report instead of placing them in the query. When the report tried to work out the calculations when there were in excess of around 200 records, this was causing the problem. Removing these calculated fields from the report solved the problem.

    Thanks.

Posting Permissions

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