Re: getting a text key field to sort in a custom way

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 Data*** 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 Data*** 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 <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.


SDave wrote:
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

.