Varible control source for field

I have users select a grouping method for reports, and that works great
(based on code from Allen Browne's site). When they choose a particular
field for grouping, I want a title box to display the name of the group - if
they choose to group by deparment, i want the title box to display
"Accounting" "Operations" "IS" etc. However, if I make the controlsource of
the text box the value of the combo box, it tells me "Department".

How can I make the control source of the text box dynamic to show the values
in the table "Department" instead of the name of the table itself?

Biggles

An alternative solution defines the sorting and grouping field in the query.
For instance if you have a form (frmPrintRpt) with an option group
(optSortBy) that allows the user to select a sorting field:
1) in the query, create a calculated column like
PriSort:Choose(Forms!frmPrintRpt!optSortBy, [Dept], [LastName], [EmpStatus])
These are three different fields in the record source that correspond with
the option group values.
2) set your first level of sorting to the field
[PriSort]
This will take on the column values of the selected field.
3) if you want a column label for the field in the report, use:
=Choose(Forms!frmPrintRpt!optSortBy, "Department", "Last Name", "Status")
--
Microsoft Access MVP


"Biggles" wrote:

I have users select a grouping method for reports, and that works great
(based on code from Allen Browne's site). When they choose a particular
field for grouping, I want a title box to display the name of the group - if
they choose to group by deparment, i want the title box to display
"Accounting" "Operations" "IS" etc. However, if I make the controlsource of
the text box the value of the combo box, it tells me "Department".

How can I make the control source of the text box dynamic to show the values
in the table "Department" instead of the name of the table itself?

Biggles