Combining SUM and IIF NULL Statement

D

dcrqueens

I have a report and it has a count at the end in a group footer. Not all of
the groups will have a value. Can Access calculate the ones that should have
a value and then if there is nothing for a group put None in the calculated
field? My SUM function is as follows:
=SUM([TotalFiles])

I have tried a variety of ways listed on the site and I cannot figure out
the right combination of IIF Null to say none when there is no value. Any
help would be greatly appreciated.
 
D

dcrqueens

Thank you Dave for the response. Is there also a way that I can have it list
it as none if there is no data to return?

Klatuu said:
You can use the Nz function to convert Nulls to Zeros:
=SUM(Nz([TotalFiles],0))
--
Dave Hargis, Microsoft Access MVP


dcrqueens said:
I have a report and it has a count at the end in a group footer. Not all of
the groups will have a value. Can Access calculate the ones that should have
a value and then if there is nothing for a group put None in the calculated
field? My SUM function is as follows:
=SUM([TotalFiles])

I have tried a variety of ways listed on the site and I cannot figure out
the right combination of IIF Null to say none when there is no value. Any
help would be greatly appreciated.
 
K

Klatuu

Sorry, I don't understand the question. Can you provide more detail, please.
--
Dave Hargis, Microsoft Access MVP


dcrqueens said:
Thank you Dave for the response. Is there also a way that I can have it list
it as none if there is no data to return?

Klatuu said:
You can use the Nz function to convert Nulls to Zeros:
=SUM(Nz([TotalFiles],0))
--
Dave Hargis, Microsoft Access MVP


dcrqueens said:
I have a report and it has a count at the end in a group footer. Not all of
the groups will have a value. Can Access calculate the ones that should have
a value and then if there is nothing for a group put None in the calculated
field? My SUM function is as follows:
=SUM([TotalFiles])

I have tried a variety of ways listed on the site and I cannot figure out
the right combination of IIF Null to say none when there is no value. Any
help would be greatly appreciated.
 
M

Marshall Barton

dcrqueens said:
I have a report and it has a count at the end in a group footer. Not all of
the groups will have a value. Can Access calculate the ones that should have
a value and then if there is nothing for a group put None in the calculated
field? My SUM function is as follows:
=SUM([TotalFiles])

I have tried a variety of ways listed on the site and I cannot figure out
the right combination of IIF Null to say none when there is no value. Any
help would be greatly appreciated.


Generally, if a group does not have a value, there will be
no group in the report. OTOH, if the group does have
records, but the value to sum has Null in all the records,
then you can use:
=Nz(Sum(x), "None")
or, if you really feel the need to use IIf:
=IIf(Sum(x) Is Null, "None", Sum(x))
 
D

dcrqueens

Actually both solutions worked for me. The statements took care of two
different questions that I had. Thanks to you both.

Klatuu said:
You can use the Nz function to convert Nulls to Zeros:
=SUM(Nz([TotalFiles],0))
--
Dave Hargis, Microsoft Access MVP


dcrqueens said:
I have a report and it has a count at the end in a group footer. Not all of
the groups will have a value. Can Access calculate the ones that should have
a value and then if there is nothing for a group put None in the calculated
field? My SUM function is as follows:
=SUM([TotalFiles])

I have tried a variety of ways listed on the site and I cannot figure out
the right combination of IIF Null to say none when there is no value. Any
help would be greatly appreciated.
 

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