Using unbound control in group for sorting

[Aderham]

I've been away from Access for awhile and can't remember how to do this.

The report has a control that calculates the index value of a Detail
Section response % compared to a control in the Report Footer. There
are group footers that also calculate an index comparing the overall
response of the group to the response contained in the control in the
footer

Detail Line: Response 1.10% Index 110
Detail Line: Response 1.00% Index 100
Group Footer: Group Response 1.05% Index 105

Detail Line: Response 1.10% Index 110
Detail Line: Response 1.20% Index 120
Group Footer: Group Response 1.15% Index 115

Report Footer: Report Response: 1.00% Index 100

Calculation for detail row index: =Abs(Round(([Resp]-[GTResp])/[GTResp]+1,2))
Calculation for group level index:
=Abs(Round(([PartResp]-[GTResp])/[GTResp]+1,2))

I want to sort the report by the calculated Index values in the Group
Footer. The name of the text box in the footer is PerfIndex

Therefore the simple reference would be =[PerfIndex] in the group entry of
the sort/group window.

I can't remenber the right object reference format to use as an
expression in the grouping window so that the report will sort on that
value. I've tried reports![samplereport].[PerfIndex] and other varients
but no success. Do I need to specify the group containing the control?
Any help on proper reference syntax would be appreciated.

I've also tried putting the calculated control in the detail record and I
stil can not reference it. The problem is the control references values that
are created in the report such as grand totals and therefore the data for the
calculation is not in the query.

The idea is to sort detail lines by a calculated index value within the
group and then sort groups by its calculated index.

Ideas? Thanks in advance for replies.

Abbott

 

[Duane Hookom]

You can't sort a report based on a control value. Your sorting and grouping
must be performed using values from the report's record source. You might be
able to combine in other queries with the values rather than relying on
control values.

 

[Aderham]

Duane

Thank you for the response. I was afraid of this. I calculate the index in
the report so that no mater how I filter the records the index reflects the
grand total of the report. It also makes it much easier to deal with
calculating the index at various grouping levels. Since I can't sort on that
control I'll have to think about how to get the aggregated values I need into
the query so they are available to the group/sort.

I think I'll stop trying to make it work now since it can't be done. This
time the software wins, next time...

You can't sort a report based on a control value. Your sorting and grouping
must be performed using values from the report's record source. You might be
able to combine in other queries with the values rather than relying on
control values.
--
Duane Hookom
Microsoft Access MVP

I've been away from Access for awhile and can't remember how to do this.

The report has a control that calculates the index value of a Detail
Section response % compared to a control in the Report Footer. There
are group footers that also calculate an index comparing the overall
response of the group to the response contained in the control in the
footer

Detail Line: Response 1.10% Index 110
Detail Line: Response 1.00% Index 100
Group Footer: Group Response 1.05% Index 105

Detail Line: Response 1.10% Index 110
Detail Line: Response 1.20% Index 120
Group Footer: Group Response 1.15% Index 115

Report Footer: Report Response: 1.00% Index 100

Calculation for detail row index: =Abs(Round(([Resp]-[GTResp])/[GTResp]+1,2))
Calculation for group level index:
=Abs(Round(([PartResp]-[GTResp])/[GTResp]+1,2))

I want to sort the report by the calculated Index values in the Group
Footer. The name of the text box in the footer is PerfIndex

Therefore the simple reference would be =[PerfIndex] in the group entry of
the sort/group window.

I can't remenber the right object reference format to use as an
expression in the grouping window so that the report will sort on that
value. I've tried reports![samplereport].[PerfIndex] and other varients
but no success. Do I need to specify the group containing the control?
Any help on proper reference syntax would be appreciated.

I've also tried putting the calculated control in the detail record and I
stil can not reference it. The problem is the control references values that
are created in the report such as grand totals and therefore the data for the
calculation is not in the query.

The idea is to sort detail lines by a calculated index value within the
group and then sort groups by its calculated index.

Ideas? Thanks in advance for replies.

Abbott