Control report grouping conditionally with a checkbox

  • Thread starter Thread starter Kevin C Niven
  • Start date Start date
K

Kevin C Niven

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 said:
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!
Click to expand...


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.
 
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 said:
I'd like to control how a report is grouped with a checkbox.
Click to expand...
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.
Click to expand...
I know I can do this in a query, but I would specifically like to do
it in the report, not the query.*
Click to expand...
Also, is there a way to change a report's query Record Source with a
control on a form?
Click to expand...
*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!
Click to expand...

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.
Click to expand...

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


Best regards,
Kevin
 
Back
Top