1. Registered User
Join Date
Jul 2003
Posts
3

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. Cavalier King Charles
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. Registered User
Join Date
Jul 2003
Posts
3

## 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. Registered User
Join Date
Jul 2003
Posts
3

## 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. Cavalier King Charles
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
•