Sorting by Expression

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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?
 
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

Ken Snell said:
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/


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



Paul said:
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

Ken Snell said:
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/


Paul said:
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
Click to expand...
Click to expand...
Click to expand...
 
Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting:

[AccessTotalsPerformance]
Click to expand...

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!
 
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/


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

[AccessTotalsPerformance]
Click to expand...

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


Ken Snell said:
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/


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

[AccessTotalsPerformance]
Click to expand...

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