Try using the NZ function to force zero for nulls
(Nz([Redeposit Analysis: RR Code 1].[CountOfRR Code],0) -
NZ([Redeposit Analysis: RR Code 2].[CountOfRR Code],0))/
[Redeposit Analysis: RR Code 1].[CountOfRR Code]
You still have a problem if [Redeposit Analysis: RR Code 1].[CountOfRR Code]
is zero, since in that case you will get a divide by zero error.
You can test for that using an IIF statement.
IIF(Nz([Redeposit Analysis: RR Code 1].[CountOfRR Code],0)=0,
, Null
, (Nz([Redeposit Analysis: RR Code 1].[CountOfRR Code],0) -
NZ([Redeposit Analysis: RR Code 2].[CountOfRR Code],0))/
[Redeposit Analysis: RR Code 1].[CountOfRR Code])
If you want to return some value other than null, replace null with that value
or an expression that will generate the value you want.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Love said:
Thanks Karl. The output is exactly the same no mater which syntax I use
(yours or mine). My question is how do I convert any blank fields from the
output of CountOfRR Code 2 to zero so that my pecentage comes out correctly?
KARL DEWEY said:
Your syntax is wrong. I assume that your table is [[Redeposit Analysis: [RR
Code 1] and you have a field [CountOfRR Code] and then another table
[Redeposit Analysis: RR Code 2] with field [CountOfRR Code].
Your formula would be ----
Expr1: ([Redeposit Analysis: RR Code 1].[CountOfRR Code] - [Redeposit
Analysis:
RR Code 2].[CountOfRR Code])/[Redeposit Analysis: RR Code 1].[CountOfRR Code]
--
Build a little, test a little.
Love Buzz said:
Hi there.
I have two queuries that are connected and everything is working fine except
that my formula isn't reflected when one of the columns is blank.
Here is my formula:
Expr1: ([Redeposit Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis:
RR Code 2.CountOfRR Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code]
How can I have any blank field for RR Code 1 or RR Code 2 reflect zero when
the field is blank?
Thanks for your help.
