Sorting by expression field

[sazi22]

I added a field that calculates percentages using 2 other fields. I want to sort by the percentage field, but it is not listed in the Sorting and Grouping dropdown list.

How do I sort by this field?

Thanks.

 

[dhookom]

Did you add the field in your report's record source query? If it's in the query, it should show in the sorting and grouping dropdown list.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[1003]

You are not limited to the drop down list of the sorting and grouping combo box, you can enter the expression performing the calculation in the same way as you would enter it in the data source field of a control:

=your_expression_goes_here

You could also consider adding the calculated field to the record source of the report:

select Your_expression as CalculatedFieldName, your_other_fields FROM your_table_or_query_name

 

[dhookom]

Good points from 1003.

One question that often gets asked is "can I sort my report by a sum from a group footer". This is not possible. You would need to create a totals query and join it to your report's record source query so the totals could be used for sorting.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[pokeymonsc]

If I can intrude, I have a similar question. I have a report from an employee database that uses birth date and hire date to figure out retirement eligibility date. The calculations are quite lengthly and took up about a page. The report is printed out by name but I would like it to be printed out by eligibility date but since the calculations are performed in the "'Detail', 'On Format'" event I don't know how to sort by this field. Is this possible?

Thanks, Lew

 

[1003]

What you could do is create a function in the report's VBA module which would take the two fields as parameters.

public function fnRetirementEligibilityDate(byval bithDate as date,byval hireDate as date) as date
'... do your calculations here
fnRetirementEligibilityDate = dtResult
end function

Then you would use the function in the sorting and grouping dialogue:
=fnRetirementEligibilityDate([bithDate],[hireDate])

 

[dhookom]

Good idea about creating a function. However, I would not create the function in the report's VBA module. A function like this belongs in a standard module of business rules. This would allow you to use the function anywhere in your application, not just the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]