# error in calculation

[hermie]

Hello
In my query I have and expression like Average: [amount] / [total]
It works good only for those field which are 0 it creates an #error in the
related field
How can I manage the expression so that I not see anymore the #error, but a
blank or 0 value?

Hope someone can help me

Herman

 

[Helen]

Hi Herman,

I had the same problem recently and solved it by creating a custom function.
This was after trying the iif(iserror(result),0,result) option without
success. My function went something like this:

Public CheckForZero(number1 as single,number2 as single) as single

if number2 = zero then
CheckForZero = 0 'or "" if you prefer
else
CheckForZero = number1/number2
end if

end function

This solution does seem a little clumsy I know, but it did work. If anyone
has a more elegant solution, I would love to read it.

Regards,

Helen

 

[Duane Hookom]

Average: IIF(Nz([Total],0) = 0,0,Nz([amount],0) / [total])

 

[Herman]

This formula works, but for the fields which contain numbers the calculation
result is different
example: field1 =25 field 2 = 9 With my expression: [field1] / [field2]
results in 2.78 With your expression it results in 0.36? But for the fields
with 0 values your expressions works great and my expressions creates an
#Error

Average: IIF(Nz([Total],0) = 0,0,Nz([amount],0) / [total])


--
Duane Hookom
MS Access MVP

Hello
In my query I have and expression like Average: [amount] / [total]
It works good only for those field which are 0 it creates an #error in the
related field
How can I manage the expression so that I not see anymore the #error,

but

a
blank or 0 value?

Hope someone can help me

Herman

 

[Duane Hookom]

I'm not sure where Field1 and Field2 came from but you some how have them
reversed.

--
Duane Hookom
MS Access MVP

This formula works, but for the fields which contain numbers the calculation
result is different
example: field1 =25 field 2 = 9 With my expression: [field1] / [field2]
results in 2.78 With your expression it results in 0.36? But for the fields
with 0 values your expressions works great and my expressions creates an
#Error

Average: IIF(Nz([Total],0) = 0,0,Nz([amount],0) / [total])


--
Duane Hookom
MS Access MVP

Hello
In my query I have and expression like Average: [amount] / [total]
It works good only for those field which are 0 it creates an #error in the
related field
How can I manage the expression so that I not see anymore the #error,

but

a
blank or 0 value?

Hope someone can help me

Herman

 

[Herman]

You are right, I reversed the fields and all is perfect now
Thanks for your support and hope Helen reads this post too
Herman

I'm not sure where Field1 and Field2 came from but you some how have them
reversed.

--
Duane Hookom
MS Access MVP

This formula works, but for the fields which contain numbers the calculation
result is different
example: field1 =25 field 2 = 9 With my expression: [field1] / [field2]
results in 2.78 With your expression it results in 0.36? But for the fields
with 0 values your expressions works great and my expressions creates an
#Error

Average: IIF(Nz([Total],0) = 0,0,Nz([amount],0) / [total])


--
Duane Hookom
MS Access MVP


Hello
In my query I have and expression like Average: [amount] / [total]
It works good only for those field which are 0 it creates an #error

in
the

related field
How can I manage the expression so that I not see anymore the

#error,
but

a
blank or 0 value?

Hope someone can help me

Herman

 

[Helen]

Yes Herman I did see Duane's message, and will definitely use his
expression. Thanks guys Cheers, Helen

You are right, I reversed the fields and all is perfect now
Thanks for your support and hope Helen reads this post too
Herman

I'm not sure where Field1 and Field2 came from but you some how have them
reversed.

--
Duane Hookom
MS Access MVP

This formula works, but for the fields which contain numbers the calculation
result is different
example: field1 =25 field 2 = 9 With my expression: [field1] / [field2]
results in 2.78 With your expression it results in 0.36? But for the fields
with 0 values your expressions works great and my expressions creates an
#Error

Average: IIF(Nz([Total],0) = 0,0,Nz([amount],0) / [total])


--
Duane Hookom
MS Access MVP


Hello
In my query I have and expression like Average: [amount] / [total]
It works good only for those field which are 0 it creates an

#error

in