Getting yes/no totals to show without - and zero without #Error 1

[netrusher]

Below is the SQL code I am using on a query. This query is
counting Safes, UnSafes, Totals and then giving the pct of
Safes. It kind of works. My issues are as follows:
Everthing that is being counted are yes/no Check Boxes so
the totals are showing up as -17 or -22 etc etc. I would
like for those to be without the -. Also if the Safe Total
is 0 and the Total is 0 I would like for the Pct Safe
answer to be 0 or Blank instead of #Error. Does anyone have
the work around for this? Thanks for all help.

SELECT 
     Sum(IIf([BodyPinchPointsSafe]=True,[BodyPinchPoints],0)) AS Safe, 
     Sum(IIf([BodyPinchPointsUnSafe]=True,[BodyPinchPoints],0)) AS Unsafe, 
     Sum(IIf([BodyPinchPoints]=True,[BodyPinchPoints],0)) AS Totals, 
              Sum(IIf([BodyPinchPointsSafe]=True,[BodyPinchPoints],0))/Sum(IIf([BodyPinchPoints]=True,[BodyPinchPoints],0)) AS [Pct Safe], ObserversSecondTbl.Categories, ObserversSecondTbl.Behaviors
FROM 
     ObserversSecondTbl
WHERE 
     (((ObserversSecondTbl.Date) Between [Forms]![LiftsQueriesReports]![StartDateTxt] And [Forms]![LiftsQueriesReports]![EndDateTxt]))
GROUP BY 
     ObserversSecondTbl.Categories, ObserversSecondTbl.Behaviors;

 

[PHV]

You may try this:

SELECT 
 Abs(Sum(IIf([BodyPinchPointsSafe]=True,[BodyPinchPoints],0))) AS Safe
,Abs(Sum(IIf([BodyPinchPointsUnSafe]=True,[BodyPinchPoints],0))) AS Unsafe
,Abs(Sum([BodyPinchPoints])) AS Totals
,IIf(Sum([BodyPinchPoints])=0,0,Sum(IIf([BodyPinchPointsSafe]=True,[BodyPinchPoints],0))/Sum([BodyPinchPoints])) AS [Pct Safe]
,Categories,Behaviors
FROM ObserversSecondTbl
WHERE [Date] Between [Forms]![LiftsQueriesReports]![StartDateTxt] And [Forms]![LiftsQueriesReports]![EndDateTxt]
GROUP BY Categories,Behaviors

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

 

[netrusher]

Thanks PH!