Re: Sorting Alpha then Numeric

Hi Chuck,
I finally got it thanks for the help.
I had to convert the chars, I used Asc([X]) and if numeric add 123, I also
had to test the length as not all had 6 chars.
First field enter:
SRT1: IIf(Left([X],1)>="0" And Left([X],1)<="9",Asc([X])+123,Asc([X]))

SRT2: IIf(Len([X])>1,IIf(Mid([X],2,1)>="0" And
Mid([X],2,1)<="9",Asc(Mid([X],2,1))+123,Asc(Mid([X],2,1))),0)

SRTJ3: IIf(Len([X])>2,IIf(Mid([X],3,1)>="0" And
Mid([X],3,1)<="9",Asc(Mid([X],3,1))+123,Asc(Mid([X],3,1))),0)

SRTJ4: IIf(Len([X])>3,IIf(Mid([X],4,1)>="0" And
Mid([X],4,1)<="9",Asc(Mid([X],4,1))+123,Asc(Mid([X],4,1))),0)

SRTJ5: IIf(Len([TBLF01FAC])>4,IIf(Mid([TBLF01FAC],5,1)>="0" And
Mid([TBLF01FAC],5,1)<="9",Asc(Mid([TBLF01FAC],5,1))+123,Asc(Mid([TBLF01FAC],5,1))),0)

SRTJ6: IIf(Len([X])>5,IIf(Mid([X],6,1)>="0" And
Mid([X],6,1)<="9",Asc(Mid([X],6,1))+123,Asc(Mid([X],6,1))),0) AS SRTJ6
--
John Melbourne


"Chuck" wrote:

On Wed, 13 Jun 2007 21:41:02 -0700, John Melbourne
<JohnMelbourne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I need to sort my report Alpha before Numeric I managed to sort by the first
position by useing the Val(left(sort,1) this made a column of 0 for alpha and
the number in first position, so it sorted OK but second position did not.
hope this makes sence. what I want to do is sort
A
|
z
1
|
2
So if I have
AAAA
AZZZ
A1AA
BAAA
BZZZ
B1AA
B999
Any help welcome.
My operating system is XP Professional Ver 2002 SP2.
I'm using Access 2003.

Just realized that Numeric data can be in the last 3 places.

You will need three blank fields and enter 3 sort order equations

First field enter:
SRT1: IIf((Mid([X],4,1)>="1" And Mid([X],4,1)<="9"),Left([X],3) & "_",[X])

Second field enter:
SRT2: IIf((Mid([X],3,1)>="1" And Mid([X],3,1)<="9"),Left([X],2) & "_" &
Right([X],1),[X])

Third field enter:
SRT3: IIf((Mid([X],2,1)>="1" And Mid([X],2,1)<="9"),Left([X],1) & "_" &
Right([X],2),[X])

Sort each field ascending.
The order of the fields is important.

The SQL statement is:
SELECT Table1.X, IIf((Mid([X],4,1)>="1" And Mid([X],4,1)<="9"),Left([X],3) &
"_",[X]) AS SRT1, IIf((Mid([X],3,1)>="1" And Mid([X],3,1)<="9"),Left([X],2) &
"_" & Right([X],1),[X]) AS SRT2, IIf((Mid([X],2,1)>="1" And
Mid([X],2,1)<="9"),Left([X],1) & "_" & Right([X],2),[X]) AS SRT3
FROM Table1
ORDER BY IIf((Mid([X],4,1)>="1" And Mid([X],4,1)<="9"),Left([X],3) & "_",[X]),
IIf((Mid([X],3,1)>="1" And Mid([X],3,1)<="9"),Left([X],2) & "_" &
Right([X],1),[X]), IIf((Mid([X],2,1)>="1" And Mid([X],2,1)<="9"),Left([X],1) &
"_" & Right([X],2),[X]);

Most likely the three sort fields can be combined into a single field with a
little work.

Just a wizard prodder
Chuck
--

.

Relevant Pages

  • Re: Regarding Opening a 1GB File for Processing.
    ... >its a text file each record is of 100 chars, ... i need to sort the ... >file based in the first field, ... A local SQL server would be another (possibly ...
    (microsoft.public.dotnet.framework.performance)
  • Re: Regarding Opening a 1GB File for Processing.
    ... its a text file each record is of 100 chars, ... file based in the first field, ... first two fields and sort the list, ... >to do during the sorting. ...
    (microsoft.public.dotnet.framework.performance)
  • Re: sort on single item only
    ... 1234 ABC 56 one-large-text-field ... they can also look like this since the first field is ... actually five chars, the 2nd field is actually four chars. ... What's your sort field? ...
    (comp.unix.shell)
  • Re: sort on single item only
    ... Man sort, option -t ... 1234 ABC 56 one-large-text-field ... they can also look like this since the first field is ... actually five chars, the 2nd field is actually four chars. ...
    (comp.unix.shell)
  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... >select is about 100 times more powerful than spreadsheets. ... 5 point moving averages from a table ... in which the first field is observation number and the second field is ... What sort of gyrations does something this simple require in a SELECT ...
    (microsoft.public.excel)