|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sorting by ExpressionHi 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? Show quoteHide quote "Paul" <paul@nospam.com> wrote in message news:uKeT4XM8JHA.1380@TK2MSFTNGP02.phx.gbl... > 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 > 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 Show quoteHide quote "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message news:OFPNEgO8JHA.4608@TK2MSFTNGP05.phx.gbl... > 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" <paul@nospam.com> wrote in message > news:uKeT4XM8JHA.1380@TK2MSFTNGP02.phx.gbl... >> 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 >> > > Are you in the Sorting & Grouping option? If yes, just use the name of the
field/control that will be used for sorting: [AccessTotalsPerformance] Show quoteHide quote "Paul" <paul@nospam.com> wrote in message news:uSPIJ4O8JHA.1492@TK2MSFTNGP03.phx.gbl... > 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 [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message > news:OFPNEgO8JHA.4608@TK2MSFTNGP05.phx.gbl... >> 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" <paul@nospam.com> wrote in message >> news:uKeT4XM8JHA.1380@TK2MSFTNGP02.phx.gbl... >>> 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 >>> >> >> > > On Fri, 19 Jun 2009 12:10:54 -0400, "Ken Snell [MVP]"
<kthsneisllis9@ncoomcastt.renaetl> wrote: >Are you in the Sorting & Grouping option? If yes, just use the name of the Ken, can you actually sort by *report controls*? I'd have thought that only>field/control that will be used for sorting: > >[AccessTotalsPerformance] 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! -- John W. Vinson [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. Show quoteHide quote "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message news:sepn35ldfipejji3rqp3r56105c8kf3ujv@4ax.com... > On Fri, 19 Jun 2009 12:10:54 -0400, "Ken Snell [MVP]" > <kthsneisllis9@ncoomcastt.renaetl> wrote: > >>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! > -- > > John W. Vinson [MVP] 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. -- Show quoteHide quoteDuane Hookom Microsoft Access MVP "Ken Snell [MVP]" wrote: > 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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message > news:sepn35ldfipejji3rqp3r56105c8kf3ujv@4ax.com... > > On Fri, 19 Jun 2009 12:10:54 -0400, "Ken Snell [MVP]" > > <kthsneisllis9@ncoomcastt.renaetl> wrote: > > > >>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! > > -- > > > > John W. Vinson [MVP] > > >  |
|||||||||||||||||||||||
Other interesting topics