Custom sort order

[Guest]

I need to create a sort key to sort a text field in alphabetic order starting
with a different letter each time the report or query is run. the user will
supply the starting letter (usually different) each time .

If the user wants to start with the letter "J" the sort order wll be:
J K L M ,,,,X Y Z A B C ,,,,H I

If the start letter is "S" the sort order will be:
S T U V W X Y Z A B C,,,,,,P Q R

I am sure the function to create the sort key will include Asc([Start
Letter]) and
Asc([Text Field]) but I cna't figure it out.



Steve S

 

[Guest]

Build a table named Sort like this with the fields AA and ascii as number
fields ---
AA Letter ascii
0 A 65
1 B 66
2 C 67
3 D 68
4 E 69
5 F 70
6 G 71
………
22 W 87
23 X 88
24 Y 89
25 Z 90

Substitute your table and field names for Carrol and Word.

SELECT Carrol.Word
FROM Sort, Carrol
WHERE (((Sort.Letter)=[Enter letter]))
ORDER BY
IIf(Asc(Left([Word],1))-[ascii]<0,Asc(Left([Word],1))-[ascii]+26,Asc(Left([Word],1))-[ascii]);

 

[John Nurick]

Hi Steve,

Something like this (which works in the Northwind sample database):

SELECT
ContactName
FROM Customers
ORDER BY
(Asc(Ucase(ContactName))
+ IIf(Asc(UCase(ContactName)) < Asc(UCase([Start Letter]))
, 26, 0)),
ContactName
;

May not work as expected if the first letter is outside the range
A-Za-z.

I need to create a sort key to sort a text field in alphabetic order starting
with a different letter each time the report or query is run. the user will
supply the starting letter (usually different) each time .

If the user wants to start with the letter "J" the sort order wll be:
J K L M ,,,,X Y Z A B C ,,,,H I

If the start letter is "S" the sort order will be:
S T U V W X Y Z A B C,,,,,,P Q R

I am sure the function to create the sort key will include Asc([Start
Letter]) and
Asc([Text Field]) but I cna't figure it out.



Steve S

 

[Guest]

Thanks John and Karl for your help. what I needed was the "+26". That was
the key I was missing. I took the code I already had, added the 26, and came
up with :

strSortKey=(Asc(strLastName])-Asc(strStartSort)+26) Mod 26

Works fine . I have an aversion to the IIF() function; it just seems kind
of cumbersome to me, but it may be just as efficient as the Mod function.

thanks again

steve S.

Hi Steve,

Something like this (which works in the Northwind sample database):

SELECT
ContactName
FROM Customers
ORDER BY
(Asc(Ucase(ContactName))
+ IIf(Asc(UCase(ContactName)) < Asc(UCase([Start Letter]))
, 26, 0)),
ContactName
;

May not work as expected if the first letter is outside the range
A-Za-z.

I need to create a sort key to sort a text field in alphabetic order starting
with a different letter each time the report or query is run. the user will
supply the starting letter (usually different) each time .

If the user wants to start with the letter "J" the sort order wll be:
J K L M ,,,,X Y Z A B C ,,,,H I

If the start letter is "S" the sort order will be:
S T U V W X Y Z A B C,,,,,,P Q R

I am sure the function to create the sort key will include Asc([Start
Letter]) and
Asc([Text Field]) but I cna't figure it out.



Steve S