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.
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.
Code:
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;