Combining SUM and IIF NULL Statement
in [Reports]
|
Prev: Unique ID Problem !!??
Next: pages limitations
From: dcrqueens on 18 Sep 2009 05:54 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.
From: Klatuu on 18 Sep 2009 08:31 You can use the Nz function to convert Nulls to Zeros: =SUM(Nz([TotalFiles],0)) -- Dave Hargis, Microsoft Access MVP "dcrqueens" wrote: > 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.
From: dcrqueens on 20 Sep 2009 06:50 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" wrote: > You can use the Nz function to convert Nulls to Zeros: > =SUM(Nz([TotalFiles],0)) > -- > Dave Hargis, Microsoft Access MVP > > > "dcrqueens" wrote: > > > 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.
From: Klatuu on 20 Sep 2009 17:09 Sorry, I don't understand the question. Can you provide more detail, please. -- Dave Hargis, Microsoft Access MVP "dcrqueens" wrote: > 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" wrote: > > > You can use the Nz function to convert Nulls to Zeros: > > =SUM(Nz([TotalFiles],0)) > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "dcrqueens" wrote: > > > > > 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.
From: Marshall Barton on 20 Sep 2009 18:39
dcrqueens wrote: >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)) -- Marsh MVP [MS Access] |