getting a text key field to sort in a custom way

getting a text key field to sort in a custom way

Post by U0Rhdm » Fri, 17 Mar 2006 10:16:27


Hi,

I'm using Access with Office 2003.

I've got a key field defined as text and I need it to be sorted as follows:
00
0
01
1
2
03
3
3
04
05
5

How can I get this key field to sort like this?

TIA,
Dave
 
 
 

getting a text key field to sort in a custom way

Post by Vincent Jo » Fri, 17 Mar 2006 11:36:38

I'm not sure exactly what you need, so I tossed in a couple of extra
values in my example.

Suppose your (text) values look something like this:

[Keys] Table Datasheet View

Key Name
--------
3
10
05
04
03
010
01
00
5
3
2
1
0

Then the following Query...

[Q_SortedList] SQL:

SELECT Keys.[Key Name]
FROM Keys
ORDER BY Val([Keys]![Key Name]),
(Left$([Keys]![Key Name],1)="0")
And Len(Trim([Keys]![Key Name]))>1;

... will spit those values out in the following order:

[Q_SortedList] Query Datasheet View:

Key Name
--------
00
0
01
1
2
03
3
3
04
05
5
010
10

This may or may not do what you want, but if not, I hope it will point
you in a helpful direction.

-- Vincent Johns < XXXX@XXXXX.COM >
Please feel free to quote anything I say here.