Sorting by Expression

[Paul]

Hi all,
I have a report that is using an expression in a group footer, to display
the 'average' of data from a field from the details. I am trying to apply a
sort to this expression field using 'Sort by expression'. the expression
displays in the Builder as: =[Reports]![duplicates above 150
percent]![Text15].
Can anyone advise if this is the correct way?
Thanks in advance

 

[Ken Snell [MVP]]

No. You need to restate the full expression in the "sort" area. Not sure how
you're trying to sort a group footer's textbox value, though?

 

[Paul]

O.k. I have changed the full expression now to: =[Reports]![Duplicates above
150 percent]![AccessTotalsPerformance]
'AccessTotalsPerformance' being the name of the Text box. This field is
displaying the average of multiple values from the details.
The value in the text box has a format of percent. I am trying to sort the
report to give me the highest values first

Thanks
Paul

No. You need to restate the full expression in the "sort" area. Not sure
how you're trying to sort a group footer's textbox value, though?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi all,
I have a report that is using an expression in a group footer, to display
the 'average' of data from a field from the details. I am trying to
apply a sort to this expression field using 'Sort by expression'. the
expression displays in the Builder as: =[Reports]![duplicates above 150
percent]![Text15].
Can anyone advise if this is the correct way?
Thanks in advance

 

[Ken Snell [MVP]]

Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting:

[AccessTotalsPerformance]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

O.k. I have changed the full expression now to: =[Reports]![Duplicates
above 150 percent]![AccessTotalsPerformance]
'AccessTotalsPerformance' being the name of the Text box. This field is
displaying the average of multiple values from the details.
The value in the text box has a format of percent. I am trying to sort
the report to give me the highest values first

Thanks
Paul

No. You need to restate the full expression in the "sort" area. Not sure
how you're trying to sort a group footer's textbox value, though?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi all,
I have a report that is using an expression in a group footer, to
display the 'average' of data from a field from the details. I am
trying to apply a sort to this expression field using 'Sort by
expression'. the expression displays in the Builder as:
=[Reports]![duplicates above 150 percent]![Text15].
Can anyone advise if this is the correct way?
Thanks in advance

 

[John W. Vinson]

Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting:

[AccessTotalsPerformance]

Ken, can you actually sort by *report controls*? I'd have thought that only
fields in the form's Recordsource query could be used for sorting; if not,
I've got to go back to some reports I've done!

 

[Ken Snell [MVP]]

I'll be doggoned... my memory failed me once again (I am getting old, it
seems). You are right, John. A control on the report cannot be used for
sorting in the Sorting & Grouping setup; it must be an expression or a field
from the report's RecordSource query/table.

Thanks for the catch.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting:

[AccessTotalsPerformance]

Ken, can you actually sort by *report controls*? I'd have thought that
only
fields in the form's Recordsource query could be used for sorting; if not,
I've got to go back to some reports I've done!

 

[Duane Hookom]

If you want to sort by an aggregate, you must create a totals query that
calculates the aggregate. Then join this totals query to your report's record
souce query so the value becomes available for sorting and grouping.

--
Duane Hookom
Microsoft Access MVP

I'll be doggoned... my memory failed me once again (I am getting old, it
seems). You are right, John. A control on the report cannot be used for
sorting in the Sorting & Grouping setup; it must be an expression or a field
from the report's RecordSource query/table.

Thanks for the catch.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting:

[AccessTotalsPerformance]

Ken, can you actually sort by *report controls*? I'd have thought that
only
fields in the form's Recordsource query could be used for sorting; if not,
I've got to go back to some reports I've done!