getting a text key field to sort in a custom way

S

SDave

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
 
V

Vincent Johns

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 <[email protected]>
Please feel free to quote anything I say here.
 
Top