Sorting based on a max function in a subreport (in Access 2003)

[CJ]

I'm trying to get a report to sort based on the results of a max function.

I've created a subreport that lists all the due date revisions for project
milestones. To that I added a max function in the report footer to pull out
the most recent due date. Then I successfully created a control on the main
form that pulls the result of that max function. The problem is that I can't
get the main report to sort based on this control.

Am I missing something or is there a better way to do this?

Thanks.

 

[Marshall Barton]

I'm trying to get a report to sort based on the results of a max function.

I've created a subreport that lists all the due date revisions for project
milestones. To that I added a max function in the report footer to pull out
the most recent due date. Then I successfully created a control on the main
form that pulls the result of that max function. The problem is that I can't
get the main report to sort based on this control.

Reports can on sort on expressions that include record
source fields. It in unaware of controls in the report and
can not work with an aggregate funcrion (e,g, Count Sum,
Max, etc)

That means you need to change the main report's record
source query to include the latest date that will be in the
subreport. That will probably involve using a subquery in a
calculaed field.

 

[Duane Hookom]

You can only sort a report based on expressions and/or fields from the
report's record source query.

The solution is to create a totals query that finds the Max due date (or
whatever value) by project (or whatever field). Add this totals query to your
report's record source so you can sort on the Max.

 

[CJ]

Thanks Duane. It took me awhile to make it work, but it's running just right
now.

CJ