Value of Sum() or Count() for empty recordset
in [Help]
|
From: Jim Devenish on 4 Feb 2010 18:09 I have a continuous form, used as a subform of another, that has a currency field named ProfitTaken. In the footer I have a Text Box with ControlSource of =Sum([ProfitTaken]) This works fine when the Form has at least one record. However when there are no records I would like to display that value 0 (zero). The Text Box only shows a blank. I have tried a number of variants but without success: =nz(Sum([ProfitTaken]),0) =IIf(Count(*) = 0, 0,nz(Sum([ProfitTaken]),0)) =IIf(nz(Count(*),0) = 0,0,nz(Sum([ProfitTaken]),0))) It appears that Sum() and Count() do not return null so I tried isMissing() and isEmpty() in place of nz() but without success. What value is returned by Sum() when there are no records? I can understand that it could be undefined but I would expect Count(*) to return zero when there are no records. Any help on how to display zero under these circumstances would be welcome. By the way, before anyone wonders why I should not be satisfied with Sum() displaying a blank, this is a trimmed down version. I need to use this value in another calculation. Jim
From: Allen Browne on 4 Feb 2010 21:51 Does your form display the new record row when there are no records? If not, you're probably facing a bug in Access that's quite messy. When there are no records to display (not even the new record row), the Detail section goes blank. The Form Header and Form Footer still show, but any attempt to work with the controls there is flawed, and the flaws are not exactly the same across all versions of Access. For an example, see the first part of this article: http://allenbrowne.com/bug-06.html One way around this is to examine the RecordCount of the Recordset (or RecordsetClone) of the form. However, there's another bug in Access 2007 that prevents this working properly in the interface (whether there are records or not.) It does work in VBA, so you might need a custom function like the one here: http://allenbrowne.com/casu-20.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jim Devenish" <internet.shopping(a)foobox.com> wrote in message news:3d9f1d15-1c16-4b6c-a460-f35b6d516d91(a)g1g2000yqi.googlegroups.com... > I have a continuous form, used as a subform of another, that has a > currency field named ProfitTaken. In the footer I have a Text Box > with ControlSource of =Sum([ProfitTaken]) > > This works fine when the Form has at least one record. However when > there are no records I would like to display that value 0 (zero). The > Text Box only shows a blank. > > I have tried a number of variants but without success: > =nz(Sum([ProfitTaken]),0) > =IIf(Count(*) = 0, 0,nz(Sum([ProfitTaken]),0)) > =IIf(nz(Count(*),0) = 0,0,nz(Sum([ProfitTaken]),0))) > > It appears that Sum() and Count() do not return null so I tried > isMissing() and isEmpty() in place of nz() but without success. > > What value is returned by Sum() when there are no records? I can > understand that it could be undefined but I would expect Count(*) to > return zero when there are no records. > > Any help on how to display zero under these circumstances would be > welcome. > > By the way, before anyone wonders why I should not be satisfied with > Sum() displaying a blank, this is a trimmed down version. I need to > use this value in another calculation. > > > Jim >
From: paii, Ron on 5 Feb 2010 08:05 "Jim Devenish" <internet.shopping(a)foobox.com> wrote in message news:3d9f1d15-1c16-4b6c-a460-f35b6d516d91(a)g1g2000yqi.googlegroups.com... > I have a continuous form, used as a subform of another, that has a > currency field named ProfitTaken. In the footer I have a Text Box > with ControlSource of =Sum([ProfitTaken]) > > This works fine when the Form has at least one record. However when > there are no records I would like to display that value 0 (zero). The > Text Box only shows a blank. > > I have tried a number of variants but without success: > =nz(Sum([ProfitTaken]),0) > =IIf(Count(*) = 0, 0,nz(Sum([ProfitTaken]),0)) > =IIf(nz(Count(*),0) = 0,0,nz(Sum([ProfitTaken]),0))) > > It appears that Sum() and Count() do not return null so I tried > isMissing() and isEmpty() in place of nz() but without success. > > What value is returned by Sum() when there are no records? I can > understand that it could be undefined but I would expect Count(*) to > return zero when there are no records. > > Any help on how to display zero under these circumstances would be > welcome. > > By the way, before anyone wonders why I should not be satisfied with > Sum() displaying a blank, this is a trimmed down version. I need to > use this value in another calculation. > > > Jim > If the problem is not the bug Allen noted, the following may work. =Sum(nz([ProfitTaken],0))
From: Jim Devenish on 5 Feb 2010 09:06 On Feb 4, 11:09 pm, Jim Devenish <internet.shopp...(a)foobox.com> wrote: > I have a continuous form, used as a subform of another, that has a > currency field named ProfitTaken. In the footer I have a Text Box > with ControlSource of =Sum([ProfitTaken]) > > This works fine when the Form has at least one record. However when > there are no records I would like to display that value 0 (zero). The > Text Box only shows a blank. > > I have tried a number of variants but without success: > =nz(Sum([ProfitTaken]),0) > =IIf(Count(*) = 0, 0,nz(Sum([ProfitTaken]),0)) > =IIf(nz(Count(*),0) = 0,0,nz(Sum([ProfitTaken]),0))) > > It appears that Sum() and Count() do not return null so I tried > isMissing() and isEmpty() in place of nz() but without success. > > What value is returned by Sum() when there are no records? I can > understand that it could be undefined but I would expect Count(*) to > return zero when there are no records. > > Any help on how to display zero under these circumstances would be > welcome. > > By the way, before anyone wonders why I should not be satisfied with > Sum() displaying a blank, this is a trimmed down version. I need to > use this value in another calculation. > > Jim Thanks Allen. I had not realised that the display was at fault. I have rearranged my design. As I said the Form in question is a subForm of another. I now have text box in the subform footer with =nz(Sum([ProfitTaken]),0) and another text box in the parent form with its control source as this. The box in the subform does not show the value when there are no records but that in the parent does. This then gets used successfully in the calculation of other controls. Ron, I am afraid that your suggestion does work for me. I do think that Allen is correct. Jim
|
Pages: 1 Prev: "Object name exists" error Next: How to change the caption of a field in a SubForm |