Combining SUM and IIF NULL Statement

  • Thread starter Thread starter dcrqueens
  • Start date Start date
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.
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...


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))
 
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.
Click to expand...
Click to expand...
 
Back
Top