Group Subtotals from conditional fields

  • Thread starter Thread starter Richard Harison
  • Start date Start date
R

Richard Harison

I am designing a report in which one field is an option group-- to choose
between "Rural" & "Urban. (The field in the underlying table holds a value
of 1 or 2, as the user selects "Rural" or "Urban" on the form bound to the
table). There is one grouping field--"County"
There is a control in the Detail section that refers to the Rural/Urban
option group.
I want to create 2 subtotals for the group, one to total each time 1
(Rural) was found, and the other to total each time 2 (Urban) was found.
I created two controls with sources as an IIF function. The first box
registers a 1 every time the Control Source (the option group Rural/Urban)
is 1, otherwise a zero. I named it IsRural. The second box does the
opposite. It registers a 1 when it finds a 2, otherwise zero. It works
fine.
The problem is when I put a text box in the Group footer at set the control
source to =Sum([IsRural]), I get a parameter box, indicating that Access
doesn't recognize my control name! This should work, but it doesn't What am
I doing wrong?
Thanks
 
Richard said:
I am designing a report in which one field is an option group-- to choose
between "Rural" & "Urban. (The field in the underlying table holds a value
of 1 or 2, as the user selects "Rural" or "Urban" on the form bound to the
table). There is one grouping field--"County"
There is a control in the Detail section that refers to the Rural/Urban
option group.
I want to create 2 subtotals for the group, one to total each time 1
(Rural) was found, and the other to total each time 2 (Urban) was found.
I created two controls with sources as an IIF function. The first box
registers a 1 every time the Control Source (the option group Rural/Urban)
is 1, otherwise a zero. I named it IsRural. The second box does the
opposite. It registers a 1 when it finds a 2, otherwise zero. It works
fine.
The problem is when I put a text box in the Group footer at set the control
source to =Sum([IsRural]), I get a parameter box, indicating that Access
doesn't recognize my control name! This should work, but it doesn't What am
Click to expand...


The aggregate functions (Count,Sum, etc) onlu operate on
fields in the form/report's record source table/query. They
are unaware of controls on the form/report.

One way to do what you want is to scrap the detail text
boxes and use expressions like one of these in the group
footer text boxes:
=Count(IIf(IsRural = 1, 1, Null))
or
=Sum(IIf(IsRural = 1, 1, 0))
or
=Abs(Sum(IsRural = 1))
 
Back
Top