report datasourcce sort order not matching the report output

[markjkubicki]

When i view the data sheet of a report's data source, the records sort as i need them to (it has a not 'normal ... ascending / descending order; and there is a function, used in the query, that has been written to address this. it has been used extensively throughout the project.)

However, in the actual report the records in it's detail section, occur in a consistent sequence, but not anything like the order ought to be. The order of records in the detail does not match the order in the datasheet of the data source.

In an attempt to find the cause, I have commented EVERY line of code behind the report. It has had no effect; still wrong.

I have tried different back-end data files. One of the 3 yields consistently correctly sorted data in the printed report; the other 2 consistently do not
(The back-ends 'should have the same table structure. To validate this, i copied the table from the working back-end into one of the other files; then moved it's data into it, and so forth. I 'thought' it solved the problem - there must be some difference in the table's structure (?) (there have been some very recent updates that may not have made it to all of the back-ends.) But it's not working correctly now (maybe I am wrong about it having worked previously; and i will try again.)

the bottom line is: I'm stumped:


why would a data source, at the query sort correctly, but in the report sort differently?


as always, with appreciation in advance,
m.

[jojowhite]

you need to put the report in Design view and add the Sort order there.

[markjkubicki]

if i sort there, my only option is A->Z or Z>A; that doesn't work for me (and i have not had to sort in that manner in other reports?)

(the sort is like this:
TA, (TA-1, TA-2, TA-3...), TB, TC ...TZ, TAA, TAB, TAC ...TAZ, TBA, TBB, TBC ...TBZ, TCA, TCB, TCC ...

[madpiet]

Do you call the function in the query that the report is based on? If you do that, then the result of the function will be included in the query, and you can use it in your recordsource etc to sort the query result set.

(Hmm... what was I thinking... the sort order in the query is ignored in the report...the report's sort order is where the sorting is enforced)

[markjkubicki]

Yes, that’s exactly how I’m doing it and when I look at the results in the datasheet view of the query, they are correct. It’s only in the report that things get wired.

[jojowhite]

maybe create another table that has TID(autonumber) and another short text field (to enter those TA, TBC, etc..).
you enter those entities in correct ascending order.

now you create a query that will join your original table with the new table and join them on the "T.." field.
include the TID on the query output.
make the query as the recordsource of your Report.
then sort your report on TID.

[jojowhite]

you can also create a sort (Report) on Expression and use your function on the expression.

[madpiet]

Okay, I give. Can you post a snippet of your database? Just enough to test the sorting in the report.
But what happens if you try to sort in the query by the result of the function you're calling?

[Gustav]

The general rule is:

- do not sort the source (query) of the report. At best, it will be ignored (as you see); at worst it will increase load time of the report
- specify sorting in the report

If you can't sort on a single field, create (calculated) fields in the query that, when sorted in some sequence, will match your requirement.

[jojowhite]

see this demo, i created zzSort table and add SortOrder field to it (increasing on the way you want the data to be in order of sequence).
SortOrder field is gapped by 50, in case you want to insert another data in between.
the i created a query qryData that joins Data table to zzSort table.
next i made qryData the recordsource of the report, rptData.
and i Sort on SortOrder field of the query.

[Welshgasman]

why would a data source, at the query sort correctly, but in the report sort differently?

Reports take no notice of the incoming data order. Been that way since day 1 of Access AFAIK.

Someone had told me there is one exception, but I cannot remember it and have never seen it.

[markjkubicki]

THNX everyone for the discussions above; lots learned; lots i had not realized.
While all of that dialogue was happening, in the course of rebuilding the report from scratch, step-by-step, was able to solve my problem:


The data source had contained a table (1 of 3) not related to the other two tables, and not included in the detail section of the report. The data from this table only populates controls in the header section of the report. As soon as i took those controls out of the report ...problem solved (except i do need to have that information in the report, in the header.)

These controls are not Text Boxes (as you might expect)... they are Labels, with VBA populating the captions with the data (me.lblContr_MUpct.caption = me.Contr_MUpct).
I changed that code to fill in the data directly from the table, rather than from the report's data source query (me.lblContr_MUpct.caption = tblMarkUps.Contr_MUpct)
-> Problem Solved!

(not certain what was happening; NTL all good.)

[June7]

Why use label controls and VBA to begin with? Why not textboxes?

Could use a subreport in header.

I built a very simple report based on a simple query and the query sort order is recognized by the report:
SELECT * FROM Holidays ORDER BY HolID DESC;

However, when I tried using that query for an existing report that appeared just as simple, it wouldn't work.