Print Page | Close Window


Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
Printed Date: 21 Jul 2018 at 10:53pm

Topic: Report_SortAsPerGrpCalc
Posted By: A.D. Tejpal
Subject: Report_SortAsPerGrpCalc
Date Posted: 02 Dec 2009 at 3:59am

Explanatory Notes

    This sample db demonstrates two alternative styles for sorting between groups as per total sum for each group, as follows:
    (1) Report R_SortByGrpSumDesc_A makes use of calculated expression in Sorting and Grouping (S&G) dialog box. The report is grouped as per publishers and these groups are sorted in descending order as per total group amount (Stock * UnitCost) for each publisher. The expression used directly in S&G dialog box is:
    =CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0))
    (2) Report R_SortByGrpSumDesc_B makes use of calculated field named GrpAmount in the source query. This field name is used directly in S&G dialog box. The expression in the source query (design grid) is:
    GrpPrice: CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0))
    (a) For (2) above, use of subquery (instead of DSum()) for arriving at the total value won't suit, attracting an error message while trying to run the report. This is because subqueries are not amenable  to direct use as source fields for group levels in an access report.
    (b) For consistent sorting results, output of Nz() function, when used in a query or S&G dialog box, is required to be converted to desired data type. 

    (a) Name of  a calculated control can not be used directly in Sorting and Grouping dialog box, which only accepts either a field name featuring in the record source or an expression.
    (b) User defined function deriving its value by referring to the calculated control itself, is also not found effective in Sorting and Grouping dialog box. This could be attributable to the fact that values held by various controls are not yet exposed in report's open event, whereas field settings for sorting and grouping are required to be enforced at this stage itself.
    (c) For a solution independent of source query, an expression based upon either a built in or a user defined function can be used in the Sorting and Grouping dialog box, as demonstrated in report style A in this sample db. 

Version: Access 2000 file format.

References: Nothing extra.


Download >>: - uploads/37/



Email: -

Print Page | Close Window