Sorting by control with an aggregate expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

C.I need to generate a report that sorts on the results of the expression
within the detail section that performs a calculation based on an expression.
I need to rank the results in descending order.

I can't figure out how to do this type of sort in one step. Is it possible?

Thanks for any help!

Bev.
 
BevC. said:
C.I need to generate a report that sorts on the results of the
expression within the detail section that performs a calculation
based on an expression. I need to rank the results in descending
order.

I can't figure out how to do this type of sort in one step. Is it
possible?

Thanks for any help!

Bev.
Click to expand...

Enter that expression in the Sorting and Groupng dialog. Put an "=" at the
front just as you do in the ControlSource you have now.
 
Rick said:
Enter that expression in the Sorting and Groupng dialog. Put an "="
at the front just as you do in the ControlSource you have now.
Click to expand...

Sorry I didn;t notice "aggregate" until after I posted (don't normally see
aggregate expressions in the details section). You cannot do that.

You would have to somehow modify the query used as the Report's Recordsource
so that those values were already calculated in the query (often by joining
to another query).
 
Thanks, Rick, but the control is an unbound control and when I put the
expression in the "sort/group" dialogue it doesn't work.
Bev.
 
I have the following expression in the "footer" section and I would like to
sort by the results.

=Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151])
 
I have the following expression in the "footer" section and I would like to
sort by the results.

=Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151])
Click to expand...
Base the report on a query.
Insert a new blank column as the first column in the query.
SortOrder: Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+ _
Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]

Sort Ascending.

Chuck
 
Thank you.

Chuck said:
I have the following expression in the "footer" section and I would like to
sort by the results.

=Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151])
Click to expand...
Base the report on a query.
Insert a new blank column as the first column in the query.
SortOrder: Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+ _
Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]

Sort Ascending.

Chuck
Click to expand...
 
Here is the expression I used:

Expr1:
Sum(Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]))

This is the error message: "You tried to execute a query that does not
include the specified expression 'sort' [which happens to be the next field
in my database and when that field was removed it simply referred to the next
field] as part of an aggregate function.

Well . . . now I really don't know what to do.

Thanks, Bev.

Chuck said:
I have the following expression in the "footer" section and I would like to
sort by the results.

=Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151])
Click to expand...
Base the report on a query.
Insert a new blank column as the first column in the query.
SortOrder: Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+ _
Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]

Sort Ascending.

Chuck
Click to expand...
 
Here is the expression I used:

Expr1:
Sum(Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]))

This is the error message: "You tried to execute a query that does not
include the specified expression 'sort' [which happens to be the next field
in my database and when that field was removed it simply referred to the next
field] as part of an aggregate function.

Well . . . now I really don't know what to do.

Thanks, Bev.

Chuck said:
I have the following expression in the "footer" section and I would like to
sort by the results.

=Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151])
Click to expand...
Base the report on a query.
Insert a new blank column as the first column in the query.
Click to expand...
Click to expand...

Copy and paste the following statement into the field of the new blank column.
SortOrder: Nz([text52])+Nz([text145])+Nz([text146])+Nz([text147])+ _
Nz([text148])+Nz([text149])+Nz([text150])+Nz([text151]
Click to expand...
Click to expand...

In the Sort row of that column, click on Ascending or Descending.

When the query is opened, you should see a field named, SortOrder.
Each row in that field should show a value which is the result of adding the
values of all the listed text boxes together. The value is unique to the row
it is in. There may be duplicates. That is OK because the data in other
fields will be different. You can sort any number of fields in the query you
want to. The query will sort by columns left to right. The sorted columns do
not have to be next to one another.


 
Back
Top