Question Count(*) does not work for null values

[sifar786]

hi,

i m trying to run a query on an MSAccess table, but it does not give me any count but only 0.

There are 3 null values which i should be getting but it does not give me the result. This query works perfectly in MSSql Server 2008.

the query is :

SELECT        COUNT(*) AS Cntr
FROM            (Modul m LEFT OUTER JOIN
                         Relation r ON m.Mod_Name = r.von)
WHERE        (m.[group] = '')

I need a workaround for this but am unable to find one. Any help would be most appreciated.

 

[rcombs4]

is m.[group] the field that is null? If so your where clause needs to be

WHERE m.[group] IS NULL

a NULL value is not the same as an empty string(field='')

 

[sifar786]

thnx. i found that out. Though why does it give me a count of 4 instead of 3?

 

[rcombs4]

Since you are doing a join its probably because there are multiple rows in the Relation table with the same id.