Avoiding dividing by zero errors using nz

[Ed Hawley]

I am sure that in the past I used the nz[function] to eliminate errors due
to dividing by zero. For example, when trying to find a percent value of
data in two tables, I subtract the first from the second then divide by the
first. Occassionally, the first number will be zero for all kinds of
reasons. When that occurs, I get an error message. How do I use the nz or
any other method to eliminate the problem?

Thanks!
Ed

 

[Van T. Dinh]

More likely, you use IIf() function like:

IIf([FirstNo] = 0, 0, ([SecondNo] - [FirstNo])/[FirstNo])

 

[George Nicholson]

Or perhaps?:

IIf(nz([FirstNo],0) = 0, 0, (nz([SecondNo],0) - [FirstNo])/[FirstNo])

--
George Nicholson

Remove 'Junk' from return address.

More likely, you use IIf() function like:

IIf([FirstNo] = 0, 0, ([SecondNo] - [FirstNo])/[FirstNo])

--
HTH
Van T. Dinh
MVP (Access)

I am sure that in the past I used the nz[function] to eliminate errors due
to dividing by zero. For example, when trying to find a percent value of
data in two tables, I subtract the first from the second then divide by
the first. Occassionally, the first number will be zero for all kinds of
reasons. When that occurs, I get an error message. How do I use the nz or
any other method to eliminate the problem?

Thanks!
Ed

 

[Ed Hawley]

George,

I have tried the IIF statement you suggested and the one suggested by Van
but neither one will work for me. I get an error that there is no aggregate
function. My current formula (before trying the nz idea) is as follows:
Stock 1% Return: sum([Sell Price 1]-[Buy Price 1])/{[Buy Price 1]))". Maybe
you can see what I am doing wrong. The one I tried was Stock 1% Return:
IIF(nz([Buy Price 1],0)=0,0, (nz([Sell Price 1), 0)-[Buy Price 1])/[Buy
Price 1]). The problem is that if the buy price is missing, it defaults to
zero and then dividing by zero causes the error.

Thanks for any help you can provide.
Ed

Or perhaps?:

IIf(nz([FirstNo],0) = 0, 0, (nz([SecondNo],0) - [FirstNo])/[FirstNo])

--
George Nicholson

Remove 'Junk' from return address.

More likely, you use IIf() function like:

IIf([FirstNo] = 0, 0, ([SecondNo] - [FirstNo])/[FirstNo])

--
HTH
Van T. Dinh
MVP (Access)

I am sure that in the past I used the nz[function] to eliminate errors
due to dividing by zero. For example, when trying to find a percent value
of data in two tables, I subtract the first from the second then divide
by the first. Occassionally, the first number will be zero for all kinds
of reasons. When that occurs, I get an error message. How do I use the nz
or any other method to eliminate the problem?

Thanks!
Ed