Sorting Problem

[kenrav]

I have a text field in one of my tables that includes alpha-numeric values
such as "A1", "A2", "A3", etc. Currently, when I sort them in a report, they
sort alpha-numerically (as they should.) However, this means that "A11"
comes before "A2". Is there anyway (programatically) I can get the output to
display "A1", "A2", A3", etc. I realize I can add another field to my table
(which would include a numerical representation of the value) and sort on
that field, but I want to see whether there's any way I can do this without
modifying my back-end tables. Thanks.

 

[RonaldoOneNil]

Create this sorting field in a query so you don't have to add a new field to
your table. Make the query instead of the table the source for your report.

 

[kenrav]

I understand that. In fact, that's how it's currently handled. The question
is how can I sort alphanumeric values in ascending order in a slightly
different way. By default, Access sorts alphanumeric values like this: A1,
A11, A2 whereas I need it to sort as follows: A1, A2, A11.

 

[John W. Vinson]

I have a text field in one of my tables that includes alpha-numeric values
such as "A1", "A2", "A3", etc. Currently, when I sort them in a report, they
sort alpha-numerically (as they should.) However, this means that "A11"
comes before "A2". Is there anyway (programatically) I can get the output to
display "A1", "A2", A3", etc. I realize I can add another field to my table
(which would include a numerical representation of the value) and sort on
that field, but I want to see whether there's any way I can do this without
modifying my back-end tables. Thanks.

If the prefix is always A (or always just one letter), include a calculated
field:

SortBy: Val(Mid([yourfield], 2))

Mid will extract the substring from the second character to the end, and Val
will convert it into a number which will sort correctly.

 

[Bob Quintal]

I understand that. In fact, that's how it's currently handled.
The question is how can I sort alphanumeric values in ascending
order in a slightly different way. By default, Access sorts
alphanumeric values like this: A1, A11, A2 whereas I need it to
sort as follows: A1, A2, A11.

In the query, create a calculated field that puts a bunch of spaces
to the left of the value, then grabs the rightmost characters

SortKey: right(space(5) & MyValue,5)
so you get (spaces shown as dots)
....A1
....A2
...A11

Or you can write an user defined function that splits the string into
a numeric part and a letter part, and puts leading spaces against
both sections

....A...1
....A...2
....A..11
...zz.123

!!!!!Air code, not tested!!!!

public function GoodSort(BadSort as string) as string
For x = 1 to len(BadSort)
if instr("0123456789",mid(BadSort,x,1)) >0 then
part2 = part2 & mid(BadSort,x,1)
else
part1 = part1 & mid(BadSort,x,1)
end if
next
GoodSort = right(space(5) & part1,5) & right(space(5) & part2,5)
end function

 

[Mike Painter]

I understand that. In fact, that's how it's currently handled. The
question is how can I sort alphanumeric values in ascending order in
a slightly different way. By default, Access sorts alphanumeric
values like this: A1, A11, A2 whereas I need it to sort as follows:
A1, A2, A11.

You can't without using calculated fields or splitting off the alpha part.