Sorting Problem
in [Forms]
|
Prev: Quit Database
Next: Combo box filter before form opens
From: kenrav on 16 Feb 2010 09:58 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.
From: RonaldoOneNil on 16 Feb 2010 10:27 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" wrote: > 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.
From: kenrav on 16 Feb 2010 11:51 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. "RonaldoOneNil" wrote: > 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" wrote: > > > 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.
From: John W. Vinson on 16 Feb 2010 12:50 On Tue, 16 Feb 2010 06:58:01 -0800, kenrav <kenrav(a)discussions.microsoft.com> wrote: >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. -- John W. Vinson [MVP]
From: Bob Quintal on 16 Feb 2010 18:22
=?Utf-8?B?a2VucmF2?= <kenrav(a)discussions.microsoft.com> wrote in news:ED77F1EA-4983-4C9D-8BDE-D0C00DF25B97(a)microsoft.com: > 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 > "RonaldoOneNil" wrote: > >> 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" wrote: >> >> > 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. -- Bob Quintal PA is y I've altered my email address. |