orderBy report property?

[jadeIT]

i am trying to use the orderby property of a report in microsoft access, but i just doesnt seem to have any effect?

i have set order BY on = true in the settings dialog in design mode

I have a form which allows the user to select a field by which the report will be ordered by.

in code i am doing the following:

Dim stDocName As String

stDocName = "MerchandiseSalesReport"

Select Case Frame21.Value

Case 1

Reports!MerchandiseSalesReport.orderBy = "[created] ASC"
DoCmd.OpenReport stDocName, acPreview

Case 2

Reports!MerchandiseSalesReport.orderBy = "[supplier] ASC"
DoCmd.OpenReport stDocName, acPreview

[...etc....]

Case Else
MsgBox "none"

End Select


however, the report loads in the same default order everytime...

i checked the orderBy value in run-time in the immidiate window and it is correctly set to what the code set it to .

 

[yippie_ky_yay]

Hey jadeIT,

I had this same problem:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33105

To sum it up - the only way to make the order reliable is to use the Sorting and Grouping button - sorry!

I'd suggest making a copy and setting the order for each. Then, rather than your criteria change according to what the user selects, have the respective report open.

-Sean

 

[yellow]

You can do what you want if you have no grouping in the report and you are doing all sorting in the report code. It may not be working because you need to put this line of code in:

[Report_MyReport].OrderByOn = True

Then again it may not be working because you have grouping in the report.

 

[jadeIT]

Yes i have set the orderby property to true

Then again it may not be working because you have grouping in the report.

what do you mean by having grouping in the report???

 

[yippie_ky_yay]

What yellow means is that you can order and group by using SQL (like I think you've done in your Query) and you can sort and group by using the button I mentioned in my post.

What I've found is that the Order and Group by in the report will often override the ones in a query. This makes sense because a lot of people use the group headers.

Check the Order and Grouping button, and if there's anything in there, then you've got sorting and grouping in your report.

-Sean

 

[jadeIT]

i got it to work, thanks everyone for your help

 

[stpiepgr]

I have similar problem that I can't work out. I have a report that has several calculated fields. I am trying to set the OrderBy to sort on one of these calculated fields but keep receiving the Enter Parameter Msgbox "calcfield". Is it possible to set the order on a calc'd field and how do I go about it? Thanks.

 

[Rich]

Put the calculated field in a query, base the report on the query

 

[jadeIT]

you cant sort, group or filter a report by anything other than the original fields from the query that the report is based on.

so that means, you wont be able to set orderBy to the calculated field, unless you calculate the field in the query first, then sort by the query field.

 

[shrove86]

I know this topic is extremely old but thanks to you guys for this. It is still relevant today. I learned something new.