Control report grouping conditionally with a checkbox

I'd like to control how a report is grouped with a checkbox.

For example, suppose I have a report that is a list of person names.
If, on a form, I have a checkbox "sort by first" checked, the report
sorts by first name. If I have uncheck the checkbox it sorts by last
name.

I know I can do this in a query, but I would specifically like to do
it in the report, not the query.*

Also, is there a way to change a report's query Record Source with a
control on a form?


Thanks,
Kevin


*because I am having trouble getting the sorts in my query to be
preserved in my reports. I don't know why this is...I was on the line
with PCHelps a week or two ago about this and they simply recommended
I sort in the report. Now that I've done that, I'd like to know how
to conditionally sort the report!

Kevin C Niven wrote:

I'd like to control how a report is grouped with a checkbox.

For example, suppose I have a report that is a list of person names.
If, on a form, I have a checkbox "sort by first" checked, the report
sorts by first name. If I have uncheck the checkbox it sorts by last
name.

I know I can do this in a query, but I would specifically like to do
it in the report, not the query.*

Also, is there a way to change a report's query Record Source with a
control on a form?


*because I am having trouble getting the sorts in my query to be
preserved in my reports. I don't know why this is...I was on the line
with PCHelps a week or two ago about this and they simply recommended
I sort in the report. Now that I've done that, I'd like to know how
to conditionally sort the report!


Sorting the query is often ineffective for reports. Sorting
and Grouping is the reliable way to sort reports.

To change (not add or remove) the report's sorting (and/or
grouping), you need to use code in the report's Open event
procedure. In your case, the code could be something like:

If Forms!theform.[sort by first] Then
Me.GroupLevel(N).ControlSource = "firstnamefield"
End If

If you don't have anything else in the report's Sorting and
Grouping, N would be 0.

See GroupLevel in VBA Help for mare details.

--
Marsh
MVP [MS Access]

Use a calculated field in the report's query like this --
MySort: IIF([Forms]![MyForm]![CheckBox] = -1, [FirstName], [LastName])
Then in report Grouping and Sorting select MySort to sort on.

"Kevin C Niven" wrote:

I'd like to control how a report is grouped with a checkbox.

For example, suppose I have a report that is a list of person names.
If, on a form, I have a checkbox "sort by first" checked, the report
sorts by first name. If I have uncheck the checkbox it sorts by last
name.

I know I can do this in a query, but I would specifically like to do
it in the report, not the query.*

Also, is there a way to change a report's query Record Source with a
control on a form?


Thanks,
Kevin


*because I am having trouble getting the sorts in my query to be
preserved in my reports. I don't know why this is...I was on the line
with PCHelps a week or two ago about this and they simply recommended
I sort in the report. Now that I've done that, I'd like to know how
to conditionally sort the report!

On Apr 21, 10:05*am, Marshall Barton wrote:
Kevin C Niven wrote:
I'd like to control how a report is grouped with a checkbox.

For example, suppose I have a report that is a list of person names.
If, on a form, I have a checkbox "sort by first" checked, the report
sorts by first name. *If I have uncheck the checkbox it sorts by last
name.

I know I can do this in a query, but I would specifically like to do
it in the report, not the query.*

Also, is there a way to change a report's query Record Source with a
control on a form?

*because I am having trouble getting the sorts in my query to be
preserved in my reports. *I don't know why this is...I was on the line
with PCHelps a week or two ago about this and they simply recommended
I sort in the report. *Now that I've done that, I'd like to know how
to conditionally sort the report!

Sorting the query is often ineffective for reports. *Sorting
and Grouping is the reliable way to sort reports.

To change (not add or remove) the report's sorting (and/or
grouping), you need to use code in the report's Open event
procedure. *In your case, the code could be something like:

* * * * If Forms!theform.[sort by first] Then
* * * * * * * * Me.GroupLevel(N).ControlSource = "firstnamefield"
* * * * End If

If you don't have anything else in the report's Sorting and
Grouping, N would be 0.

See GroupLevel in VBA Help for mare details.

--
Marsh
MVP [MS Access]

Yep, that works beautifully. Thank you, Marsh, for this EXCELLENT
answer.


Best regards,
Kevin