Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: Order by in report not working???

    Hi all,

    I am setting the order of the data displayed in my access report using vba as in the example below:

    vStrOrderBy = " val ([" & vStrOrderBy & "]) DESC;"

    Reports(ReportName).OrderBy = vStrOrderBy
    Reports(ReportName).OrderByOn = True

    but the order does not get set....any ideas why???

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ordering is trumped by the group ordering (displayed headers/footers or not) in the report design ... any external ordering is overridden. So, if you want that report to order on something else, you'll need to set it up in the report design itself ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    same answer as Mike, but stronger:
    any external ordering is overridden ignored

    ...at least, that's how it seems to me.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Iz,

    I use overridden ONLY because with all things being equal (regarding the report sorting), any external ordering will come into play when displaying record in the report. So, it does have some minimal value ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    As I was never able to figure out a way to change a report dynamically on its sort order, I used a work table as a work around. I based the report on the work table , and added a few fields at the start of the work table to be used by the report as the Sorting and/or Grouping fields. The rest of the work table looked just like the query that I wanted to use to feed data to the report. But, these added fields were named Sort01, Sort02, Sort03, etc. Then, based on how I wanted the report sorted/grouped, I would have the query put different fields from the table into these sort/group fields. For example, if I wanted the report grouped and sorted by employee state, then employee name, I would put employee state into sort01, and employee name into sort02. If I wanted the report sorted by sex, then age, then alpha by name, I would put sex into sort01, age into sort02, and employee name into sort03. It works like a charm.
    HTH,

  6. #6
    Join Date
    May 2005
    Posts
    25
    Thanks guys for all your responses - and sorry for the delay in getting back! Problemo solved

    All the variables that will be displayed in the report are populated into another table first. As the reports order will always be based on the first three fields of the populated table, I have edited the report design so that the sort order, orders by var1, var2, var3 (the col names for the pop tbl). Hope that makes sense???

    Thanks again for all your help!

Posting Permissions

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