sorting

[gi]

I have a field called ID and the records look like this:
a1, a2, a3,...a11,b1,b2...b12,c1..z199

When I try to sort, it gives me the following:

a1, a10, a11, a2, a3, a4, etc.

Instead of: a1, a2, a3, a4, ...a9, a10...etc.

Any ideas?

Thanks in advance.

 

[Ken Snell [MVP]]

Because the field is a text type field, ACCESS sorts using alphabetical
sorting, starting with the leftmost character and moving righward until a
unique string is found. Thus, in alphabetical sorting, 10 comes before 2.

Use a query that looks something like this:

SELECT MyFieldName
FROM TableName
ORDER BY Left(MyFieldName, 1),
Val(Mid(MyFieldName, 2));

 

[fredg]

I have a field called ID and the records look like this:
a1, a2, a3,...a11,b1,b2...b12,c1..z199

When I try to sort, it gives me the following:

a1, a10, a11, a2, a3, a4, etc.

Instead of: a1, a2, a3, a4, ...a9, a10...etc.

Any ideas?

Thanks in advance.

It's sorting correctly.
The Values are Text values and as text, a11 comes before a2.

You need to separate the text portion from the number portion, then
sort according to the Val() of the number string.

If the number value ALWAYS begins at the second character then you can
order by Left([Field],1), Val(Mid([Field],2))

If the letter length can vary, (a34, aaa34) you'll need to do your own
separating.

Better yet, re-think your [ID] data structure.

 

[Tom Ellison]

Dear GI:

If the first letter and subsequent numeric portions have separate
meanings, which they must if they don't sort as one, then they should
not be in the same column. While you can overcome this single issue
without separating them, it is not unlikely there will be other
problems as time goes along.

This is rule #1 of database design, "atomicity."

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts