Report subtotals using an option button group

  • 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,

Access can get confused (or cause confusion) when
calculations are based on calculations .... ie your suming
of the iif function. I am not sure but suspect it is
trying to accumulate the sum before the iif function has
executed hence the problems you are having.
The safest way out of this is to shift the iif function
back into the query as a calculated field. Use the
calculated query field on your report and sum that.

Best of luck,

Terry
 
Many, Many thanks!
I put the IIF in two calculated fields in the underlying query and had the
group footer sum each.
This also allowed me to delete the calculated fields in the form detail
section. They seemed to be the problem, because I had set the control
source to the new calculated query fields, but when I summed using their own
names, I got the same parameter box as before. As soon as I summed using
the query calculated field name, it worked great and I was able to ditch the
intermediate boxes as well. So I guess Access does not like to have
calculations refer to a box already in the report. Thanks again, Terry!!
All the Best . . .
Richard Harison

Terry said:
Richard,

Access can get confused (or cause confusion) when
calculations are based on calculations .... ie your suming
of the iif function. I am not sure but suspect it is
trying to accumulate the sum before the iif function has
executed hence the problems you are having.
The safest way out of this is to shift the iif function
back into the query as a calculated field. Use the
calculated query field on your report and sum that.

Best of luck,

Terry
Click to expand...
~~~~~~~snip
 
Back
Top