Have 0 pct show as 0.00% instead of #Error 1

[netrusher]

Below is the SQL that I am using to come up with a pct of
safe/unsafe. If there are 0 entry's for Safe Totals-Unsafe
Totals and Total totals the pct show #Error. Is there
a way for that to show as 0.00%?

SELECT SafePPEBody.Total, UnSafePPEBody.Total, TotalPPEBody.Total, [UnSafePPEBody].[Total]/[TotalPPEBody].[Total] AS [Pct Unsafe], [SafePPEBody].[Total]/[TotalPPEBody].[Total] AS [Pct Safe]
FROM TotalPPEBody, SafePPEBody, UnSafePPEBody;

 

[jrbarnett]

SELECT SafePPEBody.Total, UnSafePPEBody.Total, 
TotalPPEBody.Total, Iif([TotalPPEBody].[Total]=0, Format (0, "0.00%",[UnSafePPEBody].[Total]/[TotalPPEBody].[Total]) AS [Pct Unsafe], Iif([TotalPPEBody].[Total]=0,Format(0,"0.00%"),[SafePPEBody].[Total]/[TotalPPEBody].[Total]) AS [Pct Safe]
FROM TotalPPEBody, SafePPEBody, UnSafePPEBody;

If you don't need to perform any calculations on the values, you can just use 0 as a fixed result in a string "0.00%" rather than the format.

John

 

[PHV]

SELECT S.Total, U.Total, T.Total
, IIf(T.Total=0,0,U.Total/T.Total) AS [Pct Unsafe]
, IIf(T.Total=0,0,S.Total/T.Total) AS [Pct Safe]
FROM TotalPPEBody T, SafePPEBody S, UnSafePPEBody U;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886