Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Red face Unanswered: Report Generation Error

    I am receiving a error when I try to generate a report in MS Access that reads:

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

    The expression that is the problem is:
    =Sum(([Invoiced Amount]-[Completed Cost])/[Completed Cost])

    It appears that the only time I will have a problem with it is when "Completed Cost" is zero which leads me to think that I am having a "divide by zero" error. Can anyone suggest anything I can do?

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    replace
    Code:
    =Sum(([Invoiced Amount]-[Completed Cost])/[Completed Cost])
    with:
    Code:
    =iif([Completed Cost] = 0, 0, Sum(([Invoiced Amount]-[Completed Cost])/[Completed Cost]))
    variation:
    Code:
    =iif([Completed Cost] = 0, "Completed Cost = 0", etc
    izy

  3. #3
    Join Date
    Jul 2003
    Posts
    3

    Exclamation That code didnt fix the problem :(

    I went ahead and changed the formula to that but I am still having the same problem. If the number is 0 then I get an error but if it is not 0 then I do not get an error. Do you have any suggestions?

    Also, if you know an easier way to do this please let me know, basically that formula mentioned in my previous post is designed to calculate the percent profit I get.

    Thanks!

  4. #4
    Join Date
    Jul 2003
    Posts
    3

    Talking Got It Figured Out

    The problem with the way the code was written above is that the IIf function, when it is passed expressions will evaluate the expressions regardless of whether or not the statement evaluates false (According to Microsoft's Online Help). Therefore, I rewrote the code as follows and it worked fine

    Code:
    -----------------------------------------------------------------------------------------------------------------
    =Sum(([Invoiced Amount]-[Completed Cost])/IIf([Completed Cost]=0,1,[Completed Cost]))
    -----------------------------------------------------------------------------------------------------------------

    Thanks for the help!

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    gobsmacked!

    i accept that any expression containing a null is itself null.

    i have always thought that iif(whatever, iftrue, iffalse) did not bother evaluating the iffalse part when ifftrue. i don't have A to hand right now, but i will check tomorrow sometime.

    izyly confused

Posting Permissions

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