Math problem in a query

[RobertM]

Problem with a query I made on expression builder in
access:


Result: ([Value 1])-([Value 2]+[Value 3])

Example 1: 5000: ([10000])-([2000]+[3000])
Example 2: Null: ([10000])-([0]+[3000])
Example 3: Null: ([10000])-([0]+[0])

If there are amounts in both Value 2 and Value 3 then I
will get a result as shown in example 1.

If there is only an amount in either Value 2 or Value 3 I
will get no result as in example 2.

If values 2 and 3 are both empty I will get no result as
in example 3.

How do I straighten out this math problem?

 

[Wayne Morgan]

Null will propagate through an expression. Where you have zero, the value is
probably really a Null, not zero. Try this instead:

Nz([Value 1], 0)-Nz([Value 2],0)+Nz([Value 3],0)

This will change Nulls to zero so that you can use them in the equation.

 

[Lynn Trapp]

Wrap each of your values in a NZ() function:

NZ([Value 1],0)-NZ([Value 2],0)+NZ([Value 3],0)