Sorting Alpha then Numeric

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Don't know if this will work or not, but you could try sorting by

Replace(Replace(Replace(Replace(TheField,"0","_"),"1","_"),"2","_"),"3","_")

For all ten digits and then sort by that and then by the field. The
underscore character may not sort this in the correct order and you may need
to use some other non-alphanumeric character such as a parentheses to force
the desired sorting.

This may be too slow or may fail completely. As I said I don't know that
this will work, but it is something to try.

NOTE: The replace function is available in Access 2000 and later, although
it may not be available in Access 2000 if it is not fully patched with the
latest service packs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
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.
Click to expand...

Make a query be the record source for the report.

In the query design mode, add a blank first column and enter the following code
in the blank field:

SRT: IIf((Mid([{field name}],2,1)>="1" And Mid([{field
name}],2,1)<="9"),Left([{field name}],1) & "_" & Right([{field
name}],3),[{field name}])

Sort the query by the SRT field ascending.

Just a wizard prodder
Chuck
--
 
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.
Click to expand...

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
--
 
Hi Chuck,
I tried both methods posted I used both underscore and curley
brackets. Niether give me the sort I need maybe I need to explain further. My
file contains a state [1 number] field and a code for supplier [6 numeric
alpha], I first sort by state this is ok only contains numbers.
I then want to sort the supplier field it contains both numbers and alpha, I
want the alpha first example the data currenly sorts.
2003
200301
BIS5
BOSM1
C20123
C27010
CAC2
CET1
CPPW1
CTE2
DAN1
I want it to sort.
BIS5
BOSM1
CAC2
CET1
CPPW1
CTE2
C20123
C27010
DAN1
2003
200301
I thought this would just be a standard sort.
--
Regards,
John Melbourne


Chuck said:
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.
Click to expand...

Make a query be the record source for the report.

In the query design mode, add a blank first column and enter the following code
in the blank field:

SRT: IIf((Mid([{field name}],2,1)>="1" And Mid([{field
name}],2,1)<="9"),Left([{field name}],1) & "_" & Right([{field
name}],3),[{field name}])

Sort the query by the SRT field ascending.

Just a wizard prodder
Chuck
Click to expand...
 
On Thu, 14 Jun 2007 18:43:02 -0700, John Melbourne

John,
replace [{field name}] with [Suppliers]

I misunderstood what you have and what you wanted. The posted code
should still work but only if the 1st character of the Supplier field is always
alpha and if it doesn't mater what the 5th and 6th characters are.

Access is not forgiving about any kind of inaccuracies or inconsistencies.
When posting a question to this news group you must give as much information as
possible. The data can be fictitious, but absolutely must be representative.

It looks like the first two records of your posted data are dates, They can
not be included in the supplier field. If they are not dates, they will sort
first, not last, because the 2nd character in each case is a numeric zero.

Chuck
 
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 said:
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.
Click to expand...

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
Click to expand...
 
On Thu, 14 Jun 2007 22:39:00 -0700, John Melbourne

John,
Very cleaver approach.
Although your code will sort 2003 and 200301 last, it will also sort C1xxxx
after CTxxxx. Is this really what you want?
 
Thanks Chuck It is exactly what I need the report to do.
Thanks again for pointing me in the right direction.
--
John Melbourne


Chuck said:
On Thu, 14 Jun 2007 22:39:00 -0700, John Melbourne

John,
Very cleaver approach.
Although your code will sort 2003 and 200301 last, it will also sort C1xxxx
after CTxxxx. Is this really what you want?

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
Click to expand...
Click to expand...
 
Back
Top