Specifying unusual sort order

[Curmudgeon]

Hello. I enjoy reading these posts and wouldn't dream of bothering
you Access gurus, but I've exhausted my printed sources for the
solution to the following reporting problem. Please help if you can.

I want a specific field value to a report to be output neither in
ascending nor descending order.

Let's say ascending would be A,B, and C; descending would be C, B, and
A. I want this order: C, A, and B. Is there an expression I could
include in a query or report property tab that would produce this
result?

Regards,

David Savageau

 

[Damian S]

Hi David,

Simple solution to this one. You need to add another field to your table,
call it SortOrder. Then number the records in the order that you want them
(in the SortOrder column), then use a query like this:

select * from TABLENAME order by SortOrder

Hope this helps.

Damian.

 

[Rick Brandt]

Hello. I enjoy reading these posts and wouldn't dream of bothering
you Access gurus, but I've exhausted my printed sources for the
solution to the following reporting problem. Please help if you can.

I want a specific field value to a report to be output neither in
ascending nor descending order.

Let's say ascending would be A,B, and C; descending would be C, B, and
A. I want this order: C, A, and B. Is there an expression I could
include in a query or report property tab that would produce this
result?

Regards,

David Savageau

=Switch([FieldName]="C", 0, [FieldName]="A", 1, [FieldName]="B", 2)

If there are more than a "handful" of values then you are better off building
small table with two fields where you can map the field value to a sort value.
Then you just add that table to your query and sort on the SortValue field of
the new table.

 

[Van T. Dinh]

In most of my "Lookup" Tables (limited number of Records for look-ups,
codes, etc ...), I have a Field [SortNo] so that I can sort / order them the
way I want.

If it is not natural to include [SortNo] in your source Table for A,B,C, ...
you can create a Table [tlkpCodeSort] with 2 Fields

Code SortNo
A 20
B 30
C 10

Include Table [tlkpCodeSort] and link it to your Field

 and sort /
order your records by [SortNo].

If for some reason you want to change the sort order, simply change the
values of SortNo.