Sorting field with alpha numeric data

[saggyaccess via AccessMonster.com]

I'm using Access 2000 and I have a field which contains sample data as
follows: 4, 101 a-d, 54-56 6(a,b,c), 99, 104, 249a&249b and data type
assigned to this field is text. Since this field combines text and numbers,
is there anyway to sort this data in ascending order, also as its text field
4 comes after 39, I found that I should replace it with 04 , is there any
other method to sort this data numerically in perfect ascending order.

saggyaccess

 

[John Spencer]

Try sorting by a calculated column and then by your field.

Field: MySort: Val(NZ([YourField],"999999"))

Val returns a number value that is generated based on the leading number
characters in the string. When it gets to a non-number character it stops
generating the number.

The Nz will force a value of 999999 if YourField is null and therefore sort
the nulls to the bottom of the page. If you want them at the top, try
"-999999".
Other problems:
If yourField starts with a letter, the Val function will return zero.
54-56 and 54-1 will sort by by just 54 so they won't necessarily be in order
the order 54-1, 54-56.

 

[Gary Walter]

I'm using Access 2000 and I have a field which contains sample data as
follows: 4, 101 a-d, 54-56 6(a,b,c), 99, 104, 249a&249b and data type
assigned to this field is text. Since this field combines text and
numbers,
is there anyway to sort this data in ascending order, also as its text
field
4 comes after 39, I found that I should replace it with 04 , is there any
other method to sort this data numerically in perfect ascending order.

First, I'm not sure *how* you would
sort above in "perfect ascending order."
You probably should have listed them
out down the page as you believe that
order should be.

That way I might not have bothered
you with what I typically do.

In previous situations such as above
I usually create an extra sort field in
my query using method you already
alluded to and sort on that.

RIGHT('000000000000' & [yourfield], 12)

but you have "extra non-numbers," so assuming
"perfect" means "first digits" and all chars sort
before digits:

Right('000000000000' & CInt(Val([test])),12)

which would sort your list above as

a-d
4
6(a,b,c)
54-56
99
101
104
249&249b

good luck,

gary