Adding Null Values

  • Thread starter Thread starter Kath via AccessMonster.com
  • Start date Start date
K

Kath via AccessMonster.com

I have a question around queries and null values. I have a form that a user
will put a check in the box if Yes and leave it blank if No. I then have a
query that has to determine percentages of both positive and negative
outcomes. How would this be done? I keep getting errors and I guess I am
having a problem counting records that are only null and having it come up
with a value for those records?
 
If your field is a boolean fiield (Yes/No field) then it will never be null,
it will always be 0 (false) or -1 (True). At least in Access with the Jet
engine.

SELECT COUNT(YourField) as NumberofRecords
, COUNT(IIF(YourField = True,1,Null)) as CountTrues
, COUNT(IIF(YourField = False,1,Null)) as CountFalses
, COUNT(IIF(YourField = True,1,Null))/ Count(YourField) as PerCentTrue
FROM YourTable
 
Back
Top