Query calcualtion not calculating for empty fields

[kellem80]

Hi, I'm working in Access 2007 and have a query that bring in fields A, B, C & D, all formatted as currency. I added an expression to subtract B, C & D from A. This only work if ALL THREE (B, C & D) have amounts entered (sometimes all 3 do, more than often only one or none have amounts). The calculation is not returning the amount of field A in the expression field when B, C, & D are blank, nor is it calulating if there is an amount in just one of the three. I have typed the expression freehand and used the expression builder, same result.

I am at a loss. I have tried this both in a Report (first) and as a query (second figuring I could base the report on the Query).

Any help or insight would be appreciated!
thanks!

(Oh, how I miss excel!)

 

[kellem80]

OK, a minute after posting I learned about Nz!
For anybody else having such an issue- type Nz before each portion of the expression that may have null (empty) values. ex:
=Nz([Inventory])-Nz([Sales])+Nz([Returns])

This will subtract any sales out of inventory and add back any returns, regarless if there were any sales or any returns!!

(I still miss excel!)

 

[Joe4]

Note that NZ has a second argument to it (basically, what to return in the event of a Null value). Normally, I would write your equation a little more explicitly like this:

[COLOR=#333333]=Nz([Inventory],0)-Nz([Sales],0)+Nz([Returns],0)[/COLOR]

It is probably defaulting to zero, so it works OK in your case as you have written it.

I just want to make you aware of that, because NZ can also be used on Text fields, i.e.

=NZ([Marital_Status],"Unknown")

Also note that NZ returns a text value. However, including it in mathematical functions coerces it to a numeric result. However, if you are just returning a single number, you may need to explicitly coerce it to a number by applying a basic math function to it (like adding zero) to avoid some funny behavior (depending on how it is being used afterwards), i.e.

=NZ([Sales],0) + 0

 

[kellem80]

Thank you. That was not included in the explanation I found. I suppose as time goes on and the data may be used differently that it makes good sense to include to avoid errors.

 

[Joe4]

Here are a few links that go into it in more details, specifically on the second argument (note the second is Microsoft's official help on the NZ function):
MS Access: Nz Function
Nz Function - Access - Office.com