CODEWITHSUNDEEP
sqlms-accessms-access-2010
pbb
pbb

Reputation: 33

Calculating blank fields in MS Access via a SQL query

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.

Upvotes: 1

Views: 2202

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

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

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

Reference: In SQL is there a difference between count(*) and count(<fieldname>)

Upvotes: 3

Related Questions