total column in query not working with null

[Hani Varoqua]

Combining Text fields that contain Null values
If you're using an expression to combine two fields
containing text values and one or both of the fields
includes Null values, use the & operator instead of the +
operator to combine the values. The & operator will
combine the values even if they contain Null values, while
the + operator will return a Null if either of the two
values is a Null. For example:

FullName: [LastName] & " " & [FirstName]

 

[Dan]

Thank you! That works great for text fields, but what if
I have number fields and I want to add the two together.
But when there is a null, treat it like a zero.

-----Original Message-----
Combining Text fields that contain Null values
If you're using an expression to combine two fields
containing text values and one or both of the fields
includes Null values, use the & operator instead of the +
operator to combine the values. The & operator will
combine the values even if they contain Null values, while
the + operator will return a Null if either of the two
values is a Null. For example:

FullName: [LastName] & " " & [FirstName]

-----Original Message-----
Hello,

In a query, I have two columns with numbers. Each column
has some nulls. I created another column which is just
Column1+Column2. This works great if there are numbers in
both Column1 and Column2. However if either one has a
null. The result of the addition is also null. Does
anyone know how to fix this problem? I want to keep the
nulls null and NOT update them to zero.

Thanks!
.

.

 

[John Spencer (MVP)]

Use the NZ function.

Nz(Column1,0) + Nz(Column2,0)

If the column is null, then the NZ function will return 0 fo be used in your calculation.

Thank you! That works great for text fields, but what if
I have number fields and I want to add the two together.
But when there is a null, treat it like a zero.

-----Original Message-----
Combining Text fields that contain Null values
If you're using an expression to combine two fields
containing text values and one or both of the fields
includes Null values, use the & operator instead of the +
operator to combine the values. The & operator will
combine the values even if they contain Null values, while
the + operator will return a Null if either of the two
values is a Null. For example:

FullName: [LastName] & " " & [FirstName]

-----Original Message-----
Hello,

In a query, I have two columns with numbers. Each column
has some nulls. I created another column which is just
Column1+Column2. This works great if there are numbers in
both Column1 and Column2. However if either one has a
null. The result of the addition is also null. Does
anyone know how to fix this problem? I want to keep the
nulls null and NOT update them to zero.

Thanks!
.

.