Calculations in Report, #Num!

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have the following function in a report:
=Sum(IIf([Race Correct]="N",1,0))/Sum(IIf([Address],1,0))
*100

The problem that I am having is that if Race Correct or
Address Correct is not "N", or Null, it creates a #Num!
message. How do I tell this that if these fields are Y,
to set the value os 0?

Thank you very much
Jeff
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You might try this (unsure):

=Sum(Abs([Race Correct]="N"))/SUM(Abs([Address Correct]="N"))

If either of the columns is NULL the comparison expression will return
NULL. Sum() ignores NULLs. If the comparison expression evaluates to
False it will return ZERO. If the comparison expression evaluates to
True it will return -1 (negative one). By putting an Abs() around the
comparison we get a usable number (0 or 1). [Abs(Null) = NULL]

[Race Correct] Result
=============== ======
N 1
Something else 0
NULL Null
N 1
----
Total 2


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDO1T4echKqOuFEgEQKFcgCg6iLHWi0/g61L63QRs399pg0DXRYAn0Ck
WrGxezjbtqMcGSYHolBIuikW
=Wa9B
-----END PGP SIGNATURE-----
 
Back
Top