Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Report_SortAsPerGrpCalc
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Report_SortAsPerGrpCalc
    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.




Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.