Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting and Grouping programmatically (2000 Sp3)

    I have a report that I want to sort programmatically on three different fields using Me.GroupLevel(0).ControlSource = [Forms]![frmFinancialInfo]![txtSort] in the Open event of the form. Two of the fields work fine, the third doesn't.
    Field one is a number field - works, Field two is a text field - works, field three is a calculated (Sum) field and this is the one that fails. I get a request for a parameter when I try to open the report specifying this last field. I suspect it is because the field is calculated in the report.

    Is there a way around this?

    Peter N

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting and Grouping programmatically (2000 Sp3)

    Reports create a temporary query when they run in order to handle the grouping and sorting. That's where the problem is cropping up. Calculated controls are calculated based on values come before them in the report's recordset, so there's no way to group and sort on them that I know of.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and Grouping programmatically (2000 Sp3)

    So what options exist to view this data sorted by amount? At this point it looks like the easiest thing is to do a summary uery where amounts are calculated and then view and sort the SumOfAmount field in Excel.

    Are there any other approaches I could use?

    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting and Grouping programmatically (2000 Sp3)

    If you calculate the value in the underlying query instead of in the report, you'll be able to group and sort on it. It can be tricky and it will probably be slow unless you have a limited number of records in the table, but it's the only way I can suggest to do that. I'm having trouble envisioning a report sorted by totals, so maybe I'm missing some ideas that would help you.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting and Grouping programmatically (2000 Sp3)

    Peter,

    Try calculating this field in your report's recordsource.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and Grouping programmatically (2000 Sp3)

    The reason behind wanting to sort by totals is for a church database where the Office Manager wants to be able to view, for example the top 10 givers in a given year. I have achieved this by doing as you suggested, namely calculating the info in the query. The problem now is being able to use my filter by form to calculate the date range as well as choose the sort. This works fine for sorting by Name and by envelope number. But the date field seems to crash when I want to sort by amount, even if Amount is calculated in the underlying query. I suspect the problem has to do with there being multiple contribution dates and when it is a totalling query, the database doesn't like it. I think what I will end up doing is having a separate button on the form to output this info via automation code to excel based on this new query.

    Would this work if I, in the on click event of my button:
    1) gather up all of the contributions as a recordset for the date range entered on my form
    2) create a second recordset that does the totalling from the first
    3) send that recordset to an excel file

    This is essentially what I am now able to do by hand. I just want to automate it so it is a little easier to use and a little more foolproof. In addition, I'm trying to have all of the various financial reports printed from one input form, this is the only one that is resisting!

    Peter

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and Grouping programmatically (2000 Sp3)

    I was able to get this to do what I want in terms of automation and convenience using a command button with docmd.outputto and sending it to excel. I should be able to deal with the date range fairly easily in the query now as well.

    Thanks for your help and confirming what I suspected about sorting on calculated fields.

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •