Problem with sum when zeroes are in field...

D

Dustin

I have a report that lists fields for monthly queries. I have twelve
different queries for each month listed on the report, and I am trying to
total each category for the year at the end of the category line. My control
source is = [January] + [February] + [March.....). It works fine as long as
each month has a value, but if there is a 0 in one month for a given
category, the total is left blank.

What am I missing?
 
D

Duane Hookom

0s in fields or expression will not cause blanks in other calculations. Null
(no value) will result in the symptoms you describe. This is generally fixed
with expressions like:
=Nz( [January],0) + Nz([February],0) + Nz([March],0) ...)

I'm not sure why you need separate queries for each month but I assume you
have a good justification.
 
D

Dustin

That did it. Thanks so much.

Duane Hookom said:
0s in fields or expression will not cause blanks in other calculations. Null
(no value) will result in the symptoms you describe. This is generally fixed
with expressions like:
=Nz( [January],0) + Nz([February],0) + Nz([March],0) ...)

I'm not sure why you need separate queries for each month but I assume you
have a good justification.

--
Duane Hookom
Microsoft Access MVP


Dustin said:
I have a report that lists fields for monthly queries. I have twelve
different queries for each month listed on the report, and I am trying to
total each category for the year at the end of the category line. My control
source is = [January] + [February] + [March.....). It works fine as long as
each month has a value, but if there is a 0 in one month for a given
category, the total is left blank.

What am I missing?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top