Report Sort Order not same as Query 4

[MikeRBS]

I built a report based on a query sorted on an id like 1.1, 1.2 ... 1.10, 1.11 etc. This scheme doesn't sort properly either as a number or alpha field so I added a new field with leading zeros to sort correctly. I incorporated this in the report's query. The query sorts correctly but the report comes out in the wrong order. It's not database order, or the old query order. I can't work out what it is.

So I thought it might help if I put the new field on the report but hidden. The new field however does not appear on the field list. I've selected the query again in the record source property, but it makes no difference. I also changed the Order by On proerty to yes but that didn't make any difference either.

Can anyone tell me what is going on here?

 

[batteam]

You could try the Sorting and Grouping icon (immediately to the right of the 'Toolbox' icon) on the report's tool bar. When the little window box opens, choose the field you want to sort on under the 'field/expression' column then be sure 'Group Header' and Group Footer' are both 'no' then close the box. I've experienced the same frustration you have and many times this will work. You might have to experiment with a couple different columns, but using the 'Sorting and Grouping' box will help you out. Let me know how this turns out.

 

[MikeRBS]

I managed to solve this by putting the sort field in the Sort By property of the report. (It didn't offer any help like a field list.)

I've always assumed the data would be presented in the order the query delivers it unless over-ridden. I can't recall this situation before.

This curious thing is the bizarre ordering of the records. I think what has happenned is there is a grouping on another field (effectively the 7 in 7.1, 7.2 etc). The data would be in the correct order but I guess the report does some re-sorting. It was originally built with a wizard when there was an order by on this field. Maybe removing this ordering from the query has confused Access in some way.

I'll re-build the query and see if it is happy without a sort by value when I start afresh. Or maybe I've always specified a report detail ordering in the past and not realised that query ordering is sometimes (or always) ignored.

 

[scriverb]

You cannot rely on the queries or tables sort order to be maintained by the report. You must use the Sorting and Grouping option of the report to designate the appropriate sort order.

Just an ACCESS quirk.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MikeRBS]

Bob

Does that mean you should generally not sort your queries if they are for reports, in order to avoid two lots of sorting?

 

[scriverb]

If you are only going to use the query in a report, Yes, I leave the sorting process out of the query. You see in your reports you usually need to perform the Grouping for headers and footers, so ACCESS starts manipulating your data and it needs to do it within the report structure to accurately handle the different sections. It can't rely or even know what the sort order is from a query to perform this function. So, it requires the sorting to be done internally.

Hopefully, this will help you with your understanding of the report recordsource requirements.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MikeRBS]

Thanks chaps.

 

[Humlan]

How can I programatically set the sort order in the Sorting and Grouping dialog? I'd like to let the user change the sort order of the report based on a drop-down.

Setting the "Order By" and "Order By On" properties for the report doesn't do anything. The only thing that works is setting the sort order in the "Sorting and Grouping" dialog, but how can I access and change these values from a form? I tried to leave the values in the dialog empty, but Access complains...

Thanks in advance!

/Jonas

 

[scriverb]

Jonas: I recommend setting the OrderBy and OrderByOn properties. They do in fact work, you just have to set them correctly.

OnOpen Event Procedure of your Report"

Me.OrderBy = "Sort1Field; Sort2Field"
Me.OrderByOn = True

Now if you are going to pass parameters to the report from your form you can make direct reference to the dropdown using the following syntax:

Me.OrderBy = FORMS![[i]formname[/i]]![[i]comboboxname[/i]]

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[BobJacksonNCI]

Excellent explanation, Bob!

I ran into the sorted query / unsorted report quirk while creating my first production Access database. It's more frustrating when you're developing a basic report with no grouping or other complexities and it's out of order! I discovered that if you compact/repair the database and then run the report it comes out in order. And that's what I did (along with grumbling about Access) until I found the answer.

Bob

 

[spitzmuller]

Hi there

just a last remark: The "Sorting and Grouping" - Setting override any other ordering set using Me.OrderBy = "somefield"

I spent quite some time to figure THAT out.

Greets Simon Spitzmüller

 

[Digsys]

Thread 703-837118 might add a little to these abswers

 

[BobJacksonNCI]

Digsys,

Always working to increase my knowledge...

I don't know how to search for a Thread number.

If thread search is not available, could you copy/paste the hotlink?

Thank you!
Bob

 

[scriverb]

Just click on this thread link:

Thread703-837118

It was posted with a space and didn't show up as a link.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]