Totaling fields with Null Values

[Divvie46]

Hi All,

I have created a form that calculates a total of certain fields. All fields are in the format "real".

My total however does not work if any of my fields are left blank.
In other words, I have to actually enter a "0" in each of the blank fields to ensure that the total works.

Can you please advise on how to correct this?
It should do the following... if a field is left blank, then the respective field should automatically be assigned a "0" value.

Please get back to me regarding this as soon as possible.

[missinglinq]

In Access anything + Null is Null. You get around this by using the NZ function, which converts a Null to Zero.

For example, Instead of using

Me.Text4.Value = Me.Text0.Value + Me.Text2.Value

use

Me.Text4.Value = Nz(Me.Text0.Value) + Nz(Me.Text2.Value)

[NeoPa]

Nz Function
See AlsoApplies ToExampleSpecificsYou can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Nz(variant, [valueifnull])
The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
Note If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.



If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the value of variant.

Nz() can also be used a bit more flexibly. The ValueIfNull parameter can be non-zero if required and string fields too are handled.
Personally I prefer to set this parameter explicitly to show in the code exactly what's hapenning.
Nz() is a very valuable function to have available - especially from within SQL code.

[ADezii]

Hi All,

I have created a form that calculates a total of certain fields. All fields are in the format "real".

My total however does not work if any of my fields are left blank.
In other words, I have to actually enter a "0" in each of the blank fields to ensure that the total works.

Can you please advise on how to correct this?
It should do the following... if a field is left blank, then the respective field should automatically be assigned a "0" value.

Please get back to me regarding this as soon as possible.

As previously stated by Missingling, Nulls propagate through Expressions. If any part of an Expression evaluates to Null, the entire Expression returns Null:

(((A + B) / C) ^ D) + E = Null [if E = Null]