P
Paul Fenton
I have a report that shows monthly sales figures for the past 10
years. At the bottom of each column (months), I have 2 calculated
fields. One shows the $$ sales difference between the same month of
this year and last year. The second calculated field gives the
percentage of sales increase or decrease from last year to this year.
Looks like this:
Year Jan Feb Mar Apr etc.
2003 10 20 30 40
2004 20 30 40 50
2005 40 40 50 60
04/05 20 10 10 10
% chg 100 33,3 25 20
As long as there is a value for the difference between this year's
month and last year's, its fine. But in the months where there are no
sales totals (Nov and Dec of 2005), I'm getting "#error" in the '%
chg' field. The % change field is this:
=Format([txtNetDec]/DSum("[job$]","tClients","Month([Date Sold])=12
And Year([Date Sold])=year(date())-1"),"Percent")
where [txtNetDec] is the difference in December sales between this
year and last.
How do I get that field to calculate to zero or null or something
other thatn #error?
Paul Fenton
years. At the bottom of each column (months), I have 2 calculated
fields. One shows the $$ sales difference between the same month of
this year and last year. The second calculated field gives the
percentage of sales increase or decrease from last year to this year.
Looks like this:
Year Jan Feb Mar Apr etc.
2003 10 20 30 40
2004 20 30 40 50
2005 40 40 50 60
04/05 20 10 10 10
% chg 100 33,3 25 20
As long as there is a value for the difference between this year's
month and last year's, its fine. But in the months where there are no
sales totals (Nov and Dec of 2005), I'm getting "#error" in the '%
chg' field. The % change field is this:
=Format([txtNetDec]/DSum("[job$]","tClients","Month([Date Sold])=12
And Year([Date Sold])=year(date())-1"),"Percent")
where [txtNetDec] is the difference in December sales between this
year and last.
How do I get that field to calculate to zero or null or something
other thatn #error?
Paul Fenton