Report's automatic "SORTING"... results in... chaos

[xinyin]

I have a report bounded to a form. The form contains some invoice data which are sorted properly in terms of user defined "invoice number", the records are going accending such as 2002-001...2002-500, 2003-001...2003-299, 2003-300. When I click the "create report" button (button built by wizard) on the form, the report gives me all records from the form - but it's working really too hard - it automatically sort the records in a very silly, dummy way, such as 2003-015..2003-050,2002-090...2002-153,2003-100...2003-300!!
I tried many ways to fix this. Such as keep pressing the "A-Z" button on the form's tool bar before creating the report; set the report's "grouping & sorting" feature... all useless. I don't want to try something like making a new autonumber field as the key for the report, since the invoice number itself is already the key!
Why can't the report just follow everything straight from the form? It is "over smart" and doing really too much!!

 

[cghoga]

is the report's record source a query?

if so, within the query, set the field's sort order to ascending

 

[dhookom]

The sort order of a query has very little to do with the sort order of a report. The reliable method is to use the Sorting and Grouping Dialog. It always works the way it is supposed to work although not always as we want it to work.

What did you use for a sorting expression and how did your records display? All you should have to do is set the first sorting and grouping expression to
[Invoice Number]

Duane
MS Access MVP

 

[xinyin]

1. Yes, the form's source is a query, and the report comes from the form. I set the Invoice number's order to ascending in query design view, no effect.
2. Next, in the report's design view, I set the "Sorting and Grouping" feature, as follows:
(The general format of Invoice number: I2002-0383, I2003-0079, etc.)
Field/Expression - Invoice number
Sort Order - Ascending
Group Header - Yes
Group Footer - No
Group On - Prefix Characters (The other value can be
chosen is "each value", not suitable here.)
Group Interval - 1 (Since I only want the records to be sorted, not
grouped, and I can't type "0", so use "1" - Pick 1st
character from invoice number, which is "I",
the same for all invoice numbers, so doesn't matter.)
Keep Together - No
==> Still no effect.

A special thing is no matter what I try to do, although the records in the report are still in chaos order, however, the order is always the same, for example, it is always 0015...0037, 0500...0522, 0003, 0007...0079. Seems like Access is following some kind of "underground" sorting formula / rule / pattern when a creating a report... unfortunately that formula is terribly wrong.

 

[dhookom]

If you don't want them grouped then why did you set the Group Header to Yes?

Your entire report is only be sorted by the value "I". Since all the records begin with "I", you have no sorting performed at all. It's like telling a class of 3rd graders to line up in order of their grade. It results in mass confusion.

Either change the Group On to each value or create another grouping immediately under your current one with the Invoice Number field and set it to Group On Each Value.

Duane
MS Access MVP