View Full Version : sort by calculated field


Happy YN
03-18-2002, 05:39 PM
In a report I have a calculated control which is the difference between two numbers. Is there any way that I can sort using that control? When I refer to it in VBA in an orderby it asks for the parameter of that control since it is not an actual field. Neither can I sort by it in the sorting & grouping options again because it is not an actual field?

Pat Hartman
03-18-2002, 05:56 PM
You need to create the calculated field in your report's recordsource query. Then you can use the sort/group options of the report to sort by it.

Select ..., Nz(FldA,0) + Nz(FldB,0) As CalcFld
From ...;

Happy YN
03-18-2002, 06:25 PM
Thanx but I don't think I can do that because my control is actually the difference between a field on the report and a field on a subreport which is linked to the report. If I would be able to refer to both in one query I would do away with the subreport but I can't since sometimes the result of the query for the subreport is null and that would make the source for the main report null aswell

Pat Hartman
03-18-2002, 07:09 PM
If you can't calculate it in the query, you can't sort by it since you can ONLY sort by fields in the report's recordsource. It's as simple as that.