[AccessD] Text Field Sort Order 1.1 - 10 - 2 into 1.1 - 2 - 1 0

Those who suggest making it numeric or using the val() function have not
given thought to the values like "3.1.1".  I would write a function that
pads everything to a fixed length per level so that "3.1.1" became
"03.01.01" or "003.001.002" and "1.1" would become "001.001.000", etc.  Then
write a query using the function and order on the function value.

Regards,

Steve Goodhall



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Hewson
Sent: Monday, June 16, 2003 9:55 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Text Field Sort Order 1.1 - 10 - 2 into 1.1 - 2 -
1 0


In your querydef use the Val() function.
Sort Asc both on Val(Field) then the field.
Your query will sort by the value of the field to the second decimal point.
Then it will sort the rest.
HTH
Jim

-----Original Message-----
From: Borge Steen Hansen [mailto:pcs at azizaz.com]
Sent: Monday, June 16, 2003 8:19 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Text Field Sort Order 1.1 - 10 - 2 into 1.1 - 2 - 10


We have a text field with data like this when sorted ascending:

1
1.1
1.2
10
10.1
11
11.1
11.2
2
2.1
2.2
3
3.1.
3.1.1
3.1.2
3.1.3
4
etc etc.

Any one got ideas for how to sort the field like this:
1
1.1
1.2
2
2.1
2.2
3
3.1.
3.1.1
3.1.2
3.1.3
4
....

10
10.1
11
11.1
11.2

?????

Regards
Borge Hansen

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com