Sort order (advanced)

Topic

New Reply

Hi
This seems like a simple question, but I can’t seem to find the answer to it.
I have a computed field, and sometimes I want the result of the calculation to sort ‘last’ in a sort.
I want it even after and entry of ‘ZZZZZ’.
Access seems to do a case insensitive ASCII order, but special characters (even those after ‘z’ in the ASCII table) always sort first. So if I start the result with “{” it should sort last, but it doesn’t.
In Tools, Options, General tab I have ‘General’, but what is General order? ASCII?
My locale is “English (Australia)” which I imagine should be the same as US English.
Any ideas on:
1. What order Access actually uses, and
2. How I can get something to sort last?
Thanks for your ideas.
Peter

Reply | Quote

Replies

I have a computed field, and sometimes I want the result of the calculation to sort ‘last’ in a sort.
I want it even after and entry of ‘ZZZZZ’.

It is not clear to me what you want here.
Can you explain more what type of data you will have in this calculated field. What would the stuff look like that you want to come after zzzzz?

Reply | Quote

One option is to add another field, say “Priority”. It could be ‘0’ for all normal rows. It could be ‘1’ for the rows that should sort last. This would then be included as part of the sort order. You could use a Query against the table that would select all of the fields except Priority field, so that it wouldn’t appear in the final result. The Priority field would have to be placed before the other sort fields so that it would override them.

Reply | Quote

Hi
This seems like a simple question, but I can’t seem to find the answer to it.
I have a computed field, and sometimes I want the result of the calculation to sort ‘last’ in a sort.
I want it even after and entry of ‘ZZZZZ’.
Access seems to do a case insensitive ASCII order, but special characters (even those after ‘z’ in the ASCII table) always sort first. So if I start the result with “{” it should sort last, but it doesn’t.
In Tools, Options, General tab I have ‘General’, but what is General order? ASCII?
My locale is “English (Australia)” which I imagine should be the same as US English.
Any ideas on:
1. What order Access actually uses, and
2. How I can get something to sort last?
Thanks for your ideas.
Peter

I’m not exactly sure of the situation, but how about creating another field (lets call it SortOrder) in the query, and set it value to either 0 (most of the time) or 1 (when you want the value of your other sorted field to sort last). Then sort your query first by SortOrder and then by your calculated field. This eliminates having to put funny characters in the calculated field.

Reply | Quote