Laegge vaerdier sammen

Jeg har vist fundet svaret i Access Help

Understanding how Null values affect numeric calculations
If you use an aggregate function to calculate the sum, average, count, or another amount on a field's values, records with Null values in that field won't be included in the calculation. This is true whether you calculate the aggregate using the Total row in the query design grid, the Simple Query Wizard, or a custom expression. For example, if you use the Count function to count the number of values in a field, it will return a count of all the records with non-Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.



If you use an arithmetic operator (such as +, -, *, /) in an expression (such as [UnitsInStock]+[UnitsOnOrder]), and one of the fields in the expression contains a Null value, then the result of the entire expression will be a Null value.



Converting Null values to zero
When you have fields that contain Null values, you can create an expression that converts the Null values to zero. You might do this if you want the records containing Null values to be included in an aggregate calculation, or if you want to prevent an expression from resulting in a Null value when a field that's referenced in the expression contains Null values. Use the Nz function, for example, to convert Null values to zero:

Nz([Subtotal],0)+Nz([Freight],0)


Nz Function
       

You 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.

Syntax

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.

Remarks

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return a zero, a zero-length string, or a custom return value.

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

varResult = IIf(Nz(varFreight) > 50, "High", "Low")If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.

varResult = IIf(IsNull(varFreight), _
    "No Freight Charge", varFreight)In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge")

I din gruppering, dvs. aldersintervallerne tilføjer du kriteriet 'Or Is Null'

Det er korrekt at du er nødt til at bruge funktionen Nz når du adderer og eet eller flere af felterne er tomme (Null). Der er op til flere spørgsmål i ekspertens vidensbase om netop dette spørgsmål.

Dette eksempel:

varResult = Nz(varFreight, "No Freight Charge")

er lavet i VBA. Hvis du vil arbejde i en forespørgsel, er syntaksen lidt anderledes:

Udtryk1:(Nz([felt1];0)+Nz([felt2];0))

setting the default value to 0 could mean that the person (child) is actually 0 years old. Where NULL would mean "unknown", which therefore would also mena that those which are NULL should no be included in the result. So in your query I would set the criteria to NOT is Null which would not include thsoe which are NULL!

.

.