Sorting on a Calculated Field in a Subreport

[cinmat]

I have a report with 2 subreports. The Main report doesn't use a query but rather the table itself. The same goes for the 2 subreports which are linked to the Main table with an ID field. I have 2 calculated fields in the footer of the Main report which pull data from the 2 subreports..

[PhysicalTotal]
=IIf([Oracle subreport].Report.HasData=True,[Oracle subreport].Report!PhysicalTotal,0)

[FrozenTotal]
=IIf([TblInfo subreport].Report.HasData=True,[TblInfo subreport].Report!FrozenTotal,0)

I have a another control (Total) which has a control source of =[PhysicalTotal]-[FrozenTotal]. All the math works fine but I want to sort the report based on the Total field in the footer. Because I didn't use a query for the report, I don't know how to get the report to sort on this field. Any ideas??

 

[dhookom]

You will need to create/use queries that calculate your final value you want to sort by. Then combine these queries with your main reports record source joining the ID fields. You should then be able to place your calculated final values into the report's record source and use them for sorting.

Duane
MS Access MVP

 

[cinmat]

Duane,
I've tried placing the above code in a query but it didn't work. Perhaps I'm just not using the correct syntax??

 

[cinmat]

Duane,
Thanks very much for the assistance. I've gotten it to work. Thanks for directing me in the right direction.