Nz() not working in MS Access

I have a cross tab query that is counting Patient_ID's as the value.

Total = Nz(Count(Research.Patient_ID))+0

I have also tried:

Total = Nz(Count(Research.Patient_ID)

and....

Total = Nz(Count(Research.Patient_ID, 0)

etc. Nz() only works if at least one value is not null. However, if all of them are null, instead of seeing all 0's, I see nothing.

This is开发者_运维知识库 problematic because I am using these queries as subqueries. The main query takes the values from this one (and others like it) and adds them together. Unfortunately, if one of the subqueries is entirely null, then the sum actually turns up null, regardless of whether or not should be.

For example:

subquery1: Nz(Count(Research.Patient_ID))+0

subquery2: Nz(Count(Research.All_ID))+0

mainquery: subquery1 + subquery2


if subquery1 = 4, and subquery2 = Null...

mainquery = subquery1 + subquery2
mainquery = Null

when really...

mainquery = 4

Please help.

In this query, the main query produces the sum of counts determined in two subqueries. Is this what you're after?

SELECT
    Nz(s1.CountOfPatient_ID,0) + Nz(s2.CountOfAll_ID,0)
FROM
    [SELECT Count(Patient_ID) AS CountOfPatient_ID FROM Research]. AS s1,
    [SELECT Count(All_ID) AS CountOfAll_ID FROM Research]. AS s2

nz(null, null)+0 will always be null as null + anything is null.

If nz() can return null, just apply another nz() to handle it;

SELECT ... nz(nz([value1], [value2]), 0)

You should probably be more elaborate and post the exact part of your main query where the two counts are being added up.

So far I can only guess that you need to add NZ() to the main query expression, something like (using your pseudo-code)

mainquery = NZ(subquery1, 0) + NZ(subquery2, 0)