problems summing fields with null values

[ChuckW]

Hi,

I have a query that has four field: CustomerName,
2004Sales, 2003Sales, and 2002Sales. Many of the field
have blank values in some of the years. For instance a
customer may have sales in 2003 and 2004 but none in
2002. The 2002 value is null which I cannot change to 0.
I want to sum the three fields to get total sales. I
simply created a field and added the three together. The
problem is it will only sum for customers who have sales
in all three years. If a customer has sales in only one
or two of the years the total value is blank. Is there a
way to change this.

Thanks,

Chuck

 

[Tom Ellison]

Dear Chuck:

You can change the null values into zero. The NZ() function will do
this. Use this function on all 3 columnar values when you ask to add
them together.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts