Re: Sorting field with alpha numeric data

Tech-Archive recommends: Speed Up your PC by fixing your registry


"saggyaccess via AccessMonster.com" wrote:
I'm using Access 2000 and I have a field which contains sample data as
follows: 4, 101 a-d, 54-56 6(a,b,c), 99, 104, 249a&249b and data type
assigned to this field is text. Since this field combines text and
numbers,
is there anyway to sort this data in ascending order, also as its text
field
4 comes after 39, I found that I should replace it with 04 , is there any
other method to sort this data numerically in perfect ascending order.


First, I'm not sure *how* you would
sort above in "perfect ascending order."
You probably should have listed them
out down the page as you believe that
order should be.

That way I might not have bothered
you with what I typically do. 8-)

In previous situations such as above
I usually create an extra sort field in
my query using method you already
alluded to and sort on that.

RIGHT('000000000000' & [yourfield], 12)

but you have "extra non-numbers," so assuming
"perfect" means "first digits" and all chars sort
before digits:

Right('000000000000' & CInt(Val([test])),12)

which would sort your list above as

a-d
4
6(a,b,c)
54-56
99
101
104
249&249b

good luck,

gary


.

Relevant Pages

  • Re: Sorting Problem in Report
    ... That is a proper sort for a text field that is storing number characters. ... You must set the sort order in the report. ... ascending order. ...
    (microsoft.public.access.gettingstarted)
  • Re: Browsing the newly messed up Archive.org (continued)
    ... Orlando Enrique Fiol wrote: ... This gives you all the shows from that year in ascending order, ... That then grays out the same sort by ... I ain't a friend to change." ...
    (rec.music.gdead)
  • Sort and then cut records to Cut records and then sort
    ... cut out the records first and then sort them in ascending order. ... out the following code but fail to item into items code. ... sort in ascending order first, and then cut the records out ...
    (microsoft.public.outlook.program_vba)
  • Re: solution to evolution problem
    ... topmost). ... in ascending order so you don't have to read your ... threads backwards. ... I do sort in logical chronological order (oldest ...
    (Fedora)
  • Re: Sort and then cut records to Cut records and then sort
    ... Author of Configuring Microsoft Outlook 2003 ... >i have the following code to sort out the records into a text file. ... > cut out the records first and then sort them in ascending order. ... sort in ascending order first, and then cut the records out ...
    (microsoft.public.outlook.program_vba)