Different sort based on value in GroupLevel

[cpStar]

I have a report grouped by a location field. In the detail section it shows several columns such as 1st name, last name, city, st, zip & company name. My user would like his report sorted based on the value of that location field. For example, if the location is "NE" then sort the detail by City but if the location is "SE" then sort the detail by Company, etc. Is this possible? I should also mention that the report shows all locations it's not just one location per report.

I'm using Access 2003.

TIA
Connie

[Stewart Ross]

Hi Connie. Sorry to say that it really isn't possible to change the sort order of a report from within it according to the value of a field the way that is suggested. Report ordering is 'hard coded' into the report's grouping and ordering (it is completely independent of the underlying order of the query on which it is based). Although it can be changed under programme control this is more at a global level for the whole report, not for part of one.

If it is essential to achieve this, I suggest that you take a slightly different approach and include a composite calculated field into your report's recordsource query, one which can be based on an IIF or the like:

SortField: IIF([Location] = "NE", [City], [Company] & [whatever] & [else is required])

then sort the report on that custom field. The end result, if you get the IIF structure correct, should be the custom sort order you seek, whilst actually sorting on one field in your report (plus any other grouping) without trying to vary it dynamically.

-Stewart

[cpStar]

Hi Connie. Sorry to say that it really isn't possible to change the sort order of a report from within it according to the value of a field the way that is suggested. Report ordering is 'hard coded' into the report's grouping and ordering (it is completely independent of the underlying order of the query on which it is based). Although it can be changed under programme control this is more at a global level for the whole report, not for part of one.

If it is essential to achieve this, I suggest that you take a slightly different approach and include a composite calculated field into your report's recordsource query, one which can be based on an IIF or the like:

SortField: IIF([Location] = "NE", [City], [Company] & [whatever] & [else is required])

then sort the report on that custom field. The end result, if you get the IIF structure correct, should be the custom sort order you seek, whilst actually sorting on one field in your report (plus any other grouping) without trying to vary it dynamically.

-Stewart

Thank you Stewart for your quick response. I really didn't think it was possible as I thought sorting a GroupLevel was based on the overall GroupLevel. However, I will try your suggestion & see if that works. I'll reply before the end of week & let you know.

Thanks again for your help!
Connie