Order Text Column as a Number

[Simon Harris]

Hi All,

We have a design flaw in a database - We have pitch numbers (For holiday
home sites) in a text column, because they are either a number, or a number
followed by a letter (1, 1a...etc)

Is there anyway, without changing the table structure, that I can order a
list of these in numerical order, so I get something like:

1
1a
1b
1c
2
2a
3
4
5
5a
5b
6
7
8
9
9a
10
11
12

Any help/advice will be much appreciated.

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Guest]

Try this --
Sort_Field: Val([YourTextField)

Set the sort to ascending and uncheck the display.

 

[Douglas J. Steele]

You might actually need to sort by both the computed Sort_Field and by the
original YourTextField. Otherwise I think it's possible that you could end
up with 1, 1c, 1a, 1b, 2, ... rather than 1, 1a, 1b, 1c, 2

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this --
Sort_Field: Val([YourTextField)

Set the sort to ascending and uncheck the display.

Hi All,

We have a design flaw in a database - We have pitch numbers (For holiday
home sites) in a text column, because they are either a number, or a
number
followed by a letter (1, 1a...etc)

Is there anyway, without changing the table structure, that I can order a
list of these in numerical order, so I get something like:

1
1a
1b
1c
2
2a
3
4
5
5a
5b
6
7
8
9
9a
10
11
12

Any help/advice will be much appreciated.

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 62 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!