Calculating blank fields in MS Access via a SQL query

Question

I have a table called 'DEMO'. Under the column 'SEX' I want to find out how many blank cells I have?

I have tried:

SELECT Count(SEX) AS CountIfSexNull FROM DEMO WHERE (((DEMO.SEX) Is Null));

however, get a result of 0.

When I use 'Query Design' to select the column 'SEX' and manually filter for blanks I get the value of 2.

The query works fine on numerical fields, i.e. AGE and I get a correct answer except instead of 'Is Null' I use '0' i.e.

SELECT COUNT(DEMO_AGE) AS CountIfAgeNull FROM DEMO WHERE (DEMO.DEMO_AGE = 0);

I'm using MS Access 2010 with a .accdb database.

"" or " " is not NULL - maybe you have a default value in your column. Otherwise: more information - field type... — niyou, Sep 24 '14 at 14:07
Fionnuala's answer is correct. The problem lies in you trying to count a column which has no data within it so that's why it shows 0. By using COUNT(*) it will look at the rows instead giving you the correct answer. — TJH, Sep 24 '14 at 14:13

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

COUNT [Fieldname] does not count nulls, either use an ID or *, for example:

SELECT Count(*) AS CountIfSexNull
FROM DEMO
WHERE DEMO.SEX Is Null

edited May 23 '17 at 12:29

Community

answered Sep 24 '14 at 14:07

Fionnuala

1 Answers1