=Abs Zero error in my code?

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

I need this formula to show a 0 if if the values equal 0
right now if it is > than 0 it works but not if less than 1
then I get #num! error message






=Abs(([Additional Sample]>0)+([Additional Sample #2]>0)+([Additional Sample
#3]>0)+([Additional Sample #4]>0)+([Additional Sample #5]>0)+([Additional
Sample #6]>0)+([Additional Sample #7]>0)+([Additional Sample
#8]>0)+([Additional Sample #9]>0)+([Additional Sample #19]>0))
 
Where you may be having a problem is that the equation Null>0 returns Null,
not false. Also, once one of the values returns Null, the value of the
addition will be Null. The general rule is Null will propagate through an
equation. Try wrapping each field in Nz().

=Abs((Nz([Additional Sample],0)>0)+(Nz([Additional Sample
#2],0)>0)+(Nz([Additional Sample #3],0)>0)+(Nz([Additional Sample
#4],0)>0)+(Nz([Additional Sample #5],0)>0)+(Nz([Additional Sample
#6],0)>0)+(Nz([Additional Sample #7],0)>0)+(Nz([Additional Sample
#8],0)>0)+(Nz([Additional Sample #9],0)>0)+(Nz([Additional Sample
#19],0)>0))
 
I need this formula to show a 0 if if the values equal 0
right now if it is > than 0 it works but not if less than 1
then I get #num! error message






=Abs(([Additional Sample]>0)+([Additional Sample #2]>0)+([Additional Sample
#3]>0)+([Additional Sample #4]>0)+([Additional Sample #5]>0)+([Additional
Sample #6]>0)+([Additional Sample #7]>0)+([Additional Sample
#8]>0)+([Additional Sample #9]>0)+([Additional Sample #19]>0))
Click to expand...

I see you've gotten a good answer to the question - but please let me
suggest a "meta-answer" to the question behind your question.

If your table has all these Additional Sample #x fields, *IT IS
INCORRECTLY STRUCTURED*.

If you have a one (something) to many (samples) relationship, you
would be much better off with *TWO* tables in a one to many
relationship; the second table, AdditionalSamples, would have a
foreign key linking it to the main table, an AdditionalSampleNo (with
values drawn from the set {1, 2, 4, 5, 6, 7, 9, 19}, based on your
example), and a value field.

You would then be able to Sum or Count the records in this table using
a simple totals query, with no folderol with IIF() functions.

John W. Vinson[MVP]
 
Dave,

I tried to recreate the problem, but no matter what values I used -1, 0,
"XXX", Null, etc. I still get an answer. I was working in the immediate
window with unDimmed variables, so it may be that because the values you are
working with are in fields, or at least that is my guess based on your code.
Could be that your fields are not numeric data types. The only thing I can
suggest is doing a type conversion on each value before you do the
calculation.
 
Back
Top