Report changing Sort order

[jik_ff]

I have some data that is pulled from an SQL table with and SQL Statement. When viewing the data output, the sort order (by firstname then by lastname) looks good. When I run it through the report I made, the order gets messed up.

my report is done up something fancy to generate a phone list. In the Group,Sort and Total, I have group on the field Dummy and then grouped on the field Heads.

Dummy is a field generated at the SQL statement (1 as [Dummy]), and the grouping is made so that I can have a footer after the full detail, but before the page footer.

Heads is a field generated at the SQL statement (LEFT(" & sortField & ", 1) AS Heads) which grabs the field designated as the sort field (first name or last name), which creates the letter Header for each detailed section. For example, the data output would look something like this:

[=====A======]
Amber Thomas
Art Smith
[=====B======]
Ben Dover
Blair Witch
Burt Renolds

ext.
Well, that is what is should look like. But this order is messed up, and I'm not sure why. The data outputs properly in the query, but the order differs once it hits the report. The Heads groups are right, but the sorts between the letters is off. There is not Sort set in the Groups, Sort and Total area. Is there somewhere else that a sort could be implemented? My only Scripts for this report are to force the page to be landscape and 11x17 on Report_Open, and for formatting the height of the Detail area in the Details_Format

[dflak]

Right click on the returned data. From the resulting menu select External Data properties. Check the Preserve column sort/filter/layout box.

And / or put an order by clause in your SQL.

[jik_ff]

There is a sort (Order By) clause in my SQL statement and that works fine. In access if I view the SQL in datasheet, it is sorted perfectly. The issue is caused by the report.

I'm not sure I follow you on your first line of trouble shooting. Are you talking about the report? By default it is a print preview. I have tried viewing the report in report view, and right clicking on the report does not yield a menu with External Data Properties as an option. BTW, should say that I am using Access 2016. Please indicate what access object I should be clicking on and what state it needs to be in a the time. This sound like it may be what I am looking for...

[dflak]

My apologies: I failed to pay attention to the forum - I was answering as if this were an Excel question.

[jik_ff]

No problem. This has happened before. Ah well... Will wait for another passer by...

[jik_ff]

Ok, on rebuilding a more basic version of the phone list report, I seem to have found where the issue is occurring. It appears that the grouping for Dummy is the cause. Even though the value for Dummy is the same for every record (=1), when that group is turned on, the sort goes wacky. I cannot tell what it is try to sort by.

Ok, I guess I have to explain the reason for the Dummy Group. The phonelist populates an 11x17 page. On that page there are 6 columns. So down the first column, it starts with A, then B, then C until it runs down the bottom of the page, then pops over to the top of the next column to continue. All good. But (as a request form the powers that be) they want all the Meeting room info also listed at the end of the list. And An area for 'other' phone numbers, then a section for local emergency numbers. For this to populate at the end of the regular numbers, I cannot put it in the details footer, as it would go after each name. Can't put it in the Heads footer, or it would appear after each letter section. The footer is populated with other phone information about field offices.

Really, this is the only way I can get it to display the way they want. I need to put this stuff at the end of all the details, but before the page footer. Not sure why it is trying to sort something that has all the same value...

Basically, I need to find a way to group without sorting. Either that or I guess I could try to generate the Group and Sorting on the Report Open event? The SQL statement to create the data (and sorting) is done from selections made on hard coded combo boxes. I would need to be able to pull values from the script that calls up the report....

Ya, like to do things the hard way....

[jik_ff]

OK, found the solution, though it eluded me for a while... What I wanted to sort by was a field that was generated in the SQL statement (named OutText). This text is dynamic based on selections made from the form.

In any case, prior to making it dynamic, my grouping looked like this:
Group on Dummy
+>Group on Heads
+>Sort by LastName
+>Sort by ShowName

When I attempted to use the OutText field in the sort under the Heads group, the selection was not available. I thought it might be because the field is generated from the SQL statement. But then I noticed that a few other 'flag' fields showed up as selectable. At the bottom of the drop down was 'expression'. So I selected that, and set the expression to be [OutText]. That worked. All that running around most likely because I had too many fields to see the one I wanted on the list...