Dividing by zero in a calculated field of a query

[MC]

Hello,
I'm a first time user of this forum and most of my Access 2003 knowledge
comes from trial and error, so please be patient

Here's my problem. I'm trying to create a "Percent Change" field based on
the fields "Current Quarter Usage to date" and "Previous quarter usage". This
is the formula I have now:

Percent Change: (nz([Current Quarter Usage to date],0)-nz([Previous Quarter
Usage],0))/nz([Previous Quarter Usage],0)

This is working wherever there is a non-zero value in the "Previous Quarter
Usage" field. However, naturally wherever there is a null or zero in the
"Previous Quarter Usage" field, the "Percent Change" field returns "#Error".
What I want it to do is return 0% if there are zeros in BOTH the "Previous
Quarter Usage" and "Current Quarter Usage to date" fields, but return "N/A"
if the "Previous Quarter Usage" field is zero and the "Current Quarter Usage
to date" field is greater than zero.

Can anyone suggest a solution?
Thanks!
~MC

 

[MC]

I just tried this:

Percent Change: IIf([Previous Quarter Usage]=0,IIf([Current Quarter Usage to
date]=0,0,"NA"),(nz([Current Quarter Usage to date],0)-nz([Previous Quarter
Usage],0))/nz([Previous Quarter Usage],0))

Now it works where there are values in both Usage fields and it works
(returns 0%) if there are zeros in both Usage fields, but if there is a zero
in the Previous Quarter Usage field and a non-zero value in the Current
Quarter Usage field, it returns #Error. I want it to return "N/A", or "Not
Applicable" or something like that that tells me there is a valid reason it
can't return a %.

Any suggestions?
Thanks!

 

[Bob Barrows [MVP]]

I just tried this:

Percent Change: IIf([Previous Quarter Usage]=0,IIf([Current Quarter
Usage to date]=0,0,"NA"),(nz([Current Quarter Usage to
date],0)-nz([Previous Quarter Usage],0))/nz([Previous Quarter
Usage],0))

Now it works where there are values in both Usage fields and it works
(returns 0%) if there are zeros in both Usage fields, but if there is
a zero in the Previous Quarter Usage field and a non-zero value in
the Current Quarter Usage field, it returns #Error. I want it to
return "N/A", or "Not Applicable" or something like that that tells
me there is a valid reason it can't return a %.

IIF() can't return a string in the false part when the true part is
returning a number. And vice versa.
They need to both be the same datatype. Instead of "NA", how about
having it return an impossible %? Is a negative percentage possible? If
not, return a negative number in the false part so you will be able to
identify the ones where there is a "valid reason it can't return a %"

 

[MC]

Thanks Bob, now at least I know why it isn't working. It worked when I tried
a number instead of "NA", but since negative percent changes are possible
(and actually common in my table), I can't think of any "impossible percents."

Is there anything in Access kind of like Excel's IfError function, that just
changes what text is displayed when the formula results in an error?

Thanks in advance,
MC

I just tried this:

Percent Change: IIf([Previous Quarter Usage]=0,IIf([Current Quarter
Usage to date]=0,0,"NA"),(nz([Current Quarter Usage to
date],0)-nz([Previous Quarter Usage],0))/nz([Previous Quarter
Usage],0))

Now it works where there are values in both Usage fields and it works
(returns 0%) if there are zeros in both Usage fields, but if there is
a zero in the Previous Quarter Usage field and a non-zero value in
the Current Quarter Usage field, it returns #Error. I want it to
return "N/A", or "Not Applicable" or something like that that tells
me there is a valid reason it can't return a %.

IIF() can't return a string in the false part when the true part is
returning a number. And vice versa.
They need to both be the same datatype. Instead of "NA", how about
having it return an impossible %? Is a negative percentage possible? If
not, return a negative number in the false part so you will be able to
identify the ones where there is a "valid reason it can't return a %"


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Thanks Bob, now at least I know why it isn't working. It worked when
I tried a number instead of "NA", but since negative percent changes
are possible (and actually common in my table), I can't think of any
"impossible percents."

How about 999999999%?

Is there anything in Access kind of like Excel's IfError function,
that just changes what text is displayed when the formula results in
an error?

No. The best you can do is filter out the data that causes the error.