Sorting Alphanumeric values in a text field

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?

0
Utf
2/13/2008 4:42:03 PM
access 16762 articles. 3 followers. Follow

2 Replies
1751 Views

Similar Articles

[PageSpeed] 11

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."

"julostarr" <julostarr@discussions.microsoft.com> wrote in message 
news:D2A140F3-AC45-483E-8D4B-FE5C5FA4052E@microsoft.com...
> 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?
> 


0
Al
2/13/2008 6:53:37 PM
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.
-- 
KARL DEWEY
Build a little - Test a little


"julostarr" wrote:

> 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?
> 
0
Utf
2/13/2008 7:09:01 PM
Reply:

Similar Artilces: