Sorting Alphanumeric values in a text field

[julostarr]

I'm using Access 2003 for a database for my company. I have a field in a
table that has both text and numbers. They are part numbers, for example
21BC124. I kept the field as text because of the text with in the numbers
and didn't figure that a numeric field would alow the text. In my part
numbers table it sorts correctly (first by number then by letter then by
number again), but in my reports and queries there are a few number that sort
in the wrong place. Like this...

20D10-3
21BC123
21BC128
22D10
25TD47
21FA101
21FA200
25FA203
38FA601
21FP604
38WS100

I can't quite figure out how it is sorting, but I would like it to sort
first in numerical order the by letter then by number again in the order of
the characters, like this...

20D10-3
21BC123
21BC128
21FA101
21FA200
21FP604
22D10
25FA203
25TD47
38FA601
38WS100

Is there anyway of fixing this?

 

[Al Campagna]

Julo.
It doesn't appear that your text entries (say... PartNos) are not
consistant enough to create a "perfect" sort. There is no consistent logic
to hang your code onto.
Most PartNos have 2 digits at the start of the string, but from there on,
the letters vary in length, and some contain some dashes further on.

Try something like a claculated field in your query...
Sort1 : Val(Left(PartNo,2))
Ascending
would at least sort correctly by the first 2 digits.

You could try adding another sort field...
Sort2 : Mid(PartNo, 3, 2)
Ascending
but that won't sort perfectly either... just better.

Just experiment using string functions to get as close as possible to a
perfect sort.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[KARL DEWEY]

You might check to see if some of the records have leading spaces. You can
use the TRIM finction in your query and then check the sort.