Sorting Calculated Fields 1

[Tash]

How can I sort by a Calculated field on an Access Report? I have various fields from a query that I am using to created calculated values on a Report. I need to sort from these calculated values and can't figure it out. Any help would be appreciated. Thanks!

 

[Knicks]

If you have created calculated fields in the query, then simply go to design view of the report, on menu bar go to View, then Sorting and Grouping, choose field and sort either ascending or descending

 

[Tash]

I couldn't do these calculations in the query, so they only exist in the report. Is there a way to sort the report in VB "On Format" when the report is opened?

 

[dhookom]

You must be able to calculate the value based on the fields in the report. If you use a calculation, you must begin it with "=".

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]

 

[Knicks]

In order to sort, you'll need to create the field in the query. Dhookom is right, you should be able to take the same calculation at the report level into the query.

 

[dhookom]

Small correction. You don't have to create the field/calculation in the query. You can create the value in the Sorting and Grouping. For instance if you have StartDate and EndDate fields and want to sort by duration you can set the sorting and grouping to:
=DateDiff("D",StartDate, EndDate)
The StartDate and EndDate values must be in the report's record source.

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]

 

[Tash]

The problem is that some of my calculations on the Report are too complex for the query. I've had this happen before in a query. So I am unable to duplicate what I did in the Report's calculated fields. Is there anyway to sort the field, without it being in the query or using the sorting and grouping option on the report?

 

[dhookom]

You have been provided with the rules/requirements for sorting and grouping in a report.

You have never hinted at what your complex calculations are so no one can assist with simplifying them or providing another alternative.

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]

 

[Knicks]

When I dead ended trying to create the expression in the query to sort in report, I was able to use D-hook'em-up-a's technique.

Thanx,
I can definitely see myself using this again instead of messing around complicating my queries!