Eliminating #Error from Report

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a sales report that compares January 2005 sales to
January 2004 sales. I created a column in my query
called change in which is calculated the percentage
change in sales: (Jan05-Jan04)/Jan04. Several of our
customers are new and do not have sales for 2004. For
all of these they show up in my query and report as
#Error. I would like to eliminate the error message but
keep the 2004 and 2005 values. A blank or no value would
be ideal.

Can anyone help?

Thanks,

Chuck
 
You need to build an iif statement to look for zero values in the 2004 sales
(denominator).

This is how I would handle it:

SalesCompare: iif(2004sales=0,"",((Jan05-Jan04)/(Jan04)))

Does that make sense? You are basically saying if 2004 sales equal 0, then
type a blank space, otherwise perform that calculation. Hope that helps
 
Hi,

Thanks. Your suggestion worked. I have a follow up
question. The format for my newly created SalesCompare
field is a number such as 1.23457. I want to change this
to a percentage format. I checked both the report and
query formats and the drop downs are both blank. For the
two sales columns (2004 and 2005) the format is currency
but there is no apparent choice of percentage for my
calculated field. Any ideas about this/

Thanks,

Chuck
 
You should be able to change the format of that control when you are in
design mode of the report. Don't try to change it in the underlying query.
Is that where the salescompare field is calculated? If you are calculating
in the underlying query, I would try making a new control in the report
instead. Just yesterday I had difficulty making a control display as a %
when it was calculated in the query. So I just made the control in the
actual report and changed the properties field to percent and it worked.
Don't know why...
 
Back
Top