ORDER BY not working

[subwoofer]

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???

 

[deBassMan]

Hi there

as your code stands right now it does not appear to make sense ...

in what order do you wish your data to be presented?

 

[boblarson]

I agree -

You have: vStrOrderBy = " val ([" & vStrOrderBy & "]) DESC;"
but what gets assigned to vStrOrderBy before it gets to that line?

 

[subwoofer]

Well in short, vStrOrderBy gets assigned a column name if a certain criteria matches - the code is too long to post here. If it doesn't match the criteria it will be assigned an empty string.

The code itself is fine, apparently the external ordering is ignored by the group ordering in the reports design.

I have solved this though - so thanks for all those who took time to respond!

 

[boblarson]

When you solve a problem which you have posted, it is considered good form to post what the solution was so that it can assist others who may find this post while trying to figure out problems of their own.

 

[subwoofer]

I do usually try to post my solution, but its not always possible due to timing constraints! Many apologies for that.

My solution:
All the variables that will be displayed in the report are populated into another table first at runtime (this happens in my program for other reasons, not just for sorting the order). 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???

 

[Wiz47]

Well in short, vStrOrderBy gets assigned a column name if a certain criteria matches - the code is too long to post here. If it doesn't match the criteria it will be assigned an empty string.

The code itself is fine, apparently the external ordering is ignored by the group ordering in the reports design.
I have solved this though - so thanks for all those who took time to respond!

That is exactly what happens. It doesn't matter how you order the query externally, the internal ordering in the report takes precedence. Of course, I had to learn this truism the hard way - which resulted in me banging my head against the wall for a few days.