report questyion (1 Viewer)

[NickNeville]

Good afternoon
Could you kindly tell me if I can re-arrange the info in a report.
I have a report with results, and the results are with classes, AA , A, B, C etc
The report always shows the A class first then the AA then B etc.
Is there anyway I can tell it to produce the AA firstly then the rest ?

Many thanks
Nick

 

[Galaxiom]

Derive a field using:
IIf(Len([fieldname])=1,[fieldname] & Chr(255),[fieldname])

In your query:
ORDER BY [Derivedfield]
Uncheck the derived field Show box (or leave it out of the fields in SQL) so it doesn't display in the query result.

This will order AA, A, BB, B etc

Any of the high character codes should work at least in English. I chose 255 as it is the last of the codes used in the Chr function. I could not find an Alphanumeric sort order reference. Users with other languages might have to tweak this.


Incidentally, one way to completely arbitrarily sort a query with known values is to join it to a table which holds the sort order.
The first field of this table has all the codes you want to sort and the other field has the desired order as numbers.

Join the sort order table into the query, ORDER BY the numbered field in the joined table and uncheck the Show tickbox so it doesn't display.

Use an outer join so that values which don't have a sort order entry will still be shown. However those with no entry will sort to the top since the numbered field will be Null. Add the original field as the second ORDER BY field to order these within the nulls.