Report Sub-Total formulas where a value is null

[Lydia]

I have created a crosstab query in which some cells are null. There is
a report based on this query in which I want to create a field that
creates a sub-total for the row. As soon as the formula encounters an
empty field it returns a null result. What do I have to do to get the
formula to recognize the null as a zero?

For example

[Val1] [Val2] [Val3] Subtotal calculated

2 3 4 9
6 <null> 8 <I want 14, I'm getting a null>

I'm not having a problem creating the basic formula, I just don't know
how to account for the null values.

Lydia

 

[John Spencer]

Nz is your friend - it changes nulls to 0 (or other specified value)

Nz(Val1,0) + Nz(Val2,0) + Nz(Val3,0)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Duane Hookom]

If I understand correctly, you can create the "row" total in the crosstab
which would be more efficient than creating a complex expression in a control
source.

--
Duane Hookom
Microsoft Access MVP

Nz is your friend - it changes nulls to 0 (or other specified value)

Nz(Val1,0) + Nz(Val2,0) + Nz(Val3,0)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have created a crosstab query in which some cells are null. There is
a report based on this query in which I want to create a field that
creates a sub-total for the row. As soon as the formula encounters an
empty field it returns a null result. What do I have to do to get the
formula to recognize the null as a zero?

For example

[Val1] [Val2] [Val3] Subtotal calculated

2 3 4 9
6 <null> 8 <I want 14, I'm getting a null>

I'm not having a problem creating the basic formula, I just don't know
how to account for the null values.

Lydia