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