Share via

#NUM! result in query

Anonymous
2014-06-17T16:34:40+00:00

I am writing an expression in an access query.  When I run it I get a #NUM! result in some of the data.  My simple expression is "([New Plans]/[adjusted 80%])".  Is there some type of expression (like excel's IfError) I can use in this expression to have the #NUM! results show as a "0" or "-".

Thanks

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-06-17T17:30:07+00:00

How do the errant records differ from the others?  If the adjusted 80% column can have a value of zero, then your expression would be attempting to divide by zero, which is a mathematical impossibility of course.  If this is the situation try:

IIf(Nz([adjusted 80%],0) = 0,0,[New Plans]/[adjusted 80%])

Was this answer helpful?

3 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-17T17:45:07+00:00

    Worked perfectly - Thank you!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-06-17T17:01:07+00:00

    Thank you for the quick response, but I tried the expression and am still get the same #NUM! for some of my results.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-06-17T16:46:58+00:00

    Try...

    (Nz([New Plans],0)/Nz([adjusted 80%],0)

    Was this answer helpful?

    0 comments