count on aggregate function

[MICKI0220]

I have a side field in the detail section of a report that needs to count all the fields that have a value in it. The problem I am running into is that these fields are the product of a summation of another field. Therefor I get an aggregate function error. For example field 1's control source is the sum of Monday. field 2's control source is the sum of Tuesday etc. The field I am trying to populate would then be a count of field 1 + field 2 + field 3 etc, where the value is greater than zero. The equation result should give me no more than the number 7 for seven days, but I have only been successful getting the sum of all the values or that aggregate function error.

Thanks
Micki

 

[dhookom]

Fields don't have control sources. Are you attempting to count the number of controls in a section where the value of the control is greater than zero? Is it possible for the value of the control to be null? What are the control sources of the text boxes?

I expect you could try something like:

=Abs((txtBox1)>0 + (txtBox2)>0 + (txtBox3)>0...)

Have fields that are the names of days of the week some times suggests an un-normalized table structure.


Duane
Hook'D on Access
MS Access MVP