Sorting Characters

[Steve S]

I need to sort an text field in alphabetic orderstarting with a different
first character each time it is sorted.

A sort of A B C D,,,,,X Y Z one day should start at the letter J the next
day giving
J K L M,,,,,X Y Z A B C ,,,H I
On the third day start with S to produce
S T U V X Y Z A B C D,,,,,P Q R

I just need to sort on the first character of the field.

also is there a way to capture the numeric value of a text character

any help is appreciated.

Steve

 

[John W. Vinson]

I need to sort an text field in alphabetic orderstarting with a different
first character each time it is sorted.

A sort of A B C D,,,,,X Y Z one day should start at the letter J the next
day giving
J K L M,,,,,X Y Z A B C ,,,H I
On the third day start with S to produce
S T U V X Y Z A B C D,,,,,P Q R

I just need to sort on the first character of the field.

also is there a way to capture the numeric value of a text character

Yes, and that's what you'll need to use to do this peculiar sort. Use

Asc([textfield])

to extract the ASCII value of the first character of the text field; A=65,
J=74, S=83. If you're willing to have the third set start with T instead of S,
you can sort by

(Asc([textfield]) + 9*(DatePart("y",Date()) MOD 3) - 64) MOD 27

a thoroughly wierd function arrived using half an hour's experimentation in
Excel. This will give a different sort order every day based on the day of the
month; you can use DatePart("y", Date()) in place of Day(Date()) if you want
to avoid month-end anomalies.

Thanks for a fun challenge!

John W. Vinson [MVP]

 

[Steve S]

Thanks much John. That is what I need but I do not understand the the need
for the "Mod 3" I know how MOD works but do not understand how it affects
the result of this function. I see that the 9 is the number of letters to
skip and the MOD 27 limits the results to less than 27.

as part of learning how functions work I would appreciate an explaination of
the MOD 3

Steve

I need to sort an text field in alphabetic orderstarting with a different
first character each time it is sorted.

A sort of A B C D,,,,,X Y Z one day should start at the letter J the next
day giving
J K L M,,,,,X Y Z A B C ,,,H I
On the third day start with S to produce
S T U V X Y Z A B C D,,,,,P Q R

I just need to sort on the first character of the field.

also is there a way to capture the numeric value of a text character

Yes, and that's what you'll need to use to do this peculiar sort. Use

Asc([textfield])

to extract the ASCII value of the first character of the text field; A=65,
J=74, S=83. If you're willing to have the third set start with T instead of S,
you can sort by

(Asc([textfield]) + 9*(DatePart("y",Date()) MOD 3) - 64) MOD 27

a thoroughly wierd function arrived using half an hour's experimentation in
Excel. This will give a different sort order every day based on the day of the
month; you can use DatePart("y", Date()) in place of Day(Date()) if you want
to avoid month-end anomalies.

Thanks for a fun challenge!

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks much John. That is what I need but I do not understand the the need
for the "Mod 3" I know how MOD works but do not understand how it affects
the result of this function. I see that the 9 is the number of letters to
skip and the MOD 27 limits the results to less than 27.

as part of learning how functions work I would appreciate an explaination of
the MOD 3

I'm trying to get a factor 0, 1 or 2 depending on the day of the year (so the
factor will change every day). The MOD 27 is intended to "rotate" the sort key
through the alphabet; adding 0, 9 or 18 (9*Day MOD 3) to the position of the
letter in the alphabet gives 1-26, 10-35, 19-44. The Modulo takes the values
beyond 26 and moves them back to the beginning of the series.

John W. Vinson [MVP]

 

[Steve S]

thanks again. Your explaination certainly helped.

Steve