Special Sort in Reports

[NewBie12345]

Hello all!

I am trying to sort an alpha-numeric field in my reports with poor results. The data is similar to an outline format (1, 1A, 2, 2A, ... , 10, 10A, ... up to 50). Access returns (1, 1A, 10, 10A, 11, ... , 2, 2A ... and so on).

I would appreciate any help in this matter.

 

[WildHare]

Computers sure are dumb, aren't they... LOL

You've encountered the problem with doing an ASCII sort on values like this. It's just the way computers do it...

You're probably going to need to split apart your fields (even if just temporarily in a query) to get the NUMBER part and the ALPHA part, and sort by Alpha part within Number part.

Thus, you'll break 1.A in to 1 and A, 2.B into 2 and B, and 10.A into 10 and B.

Your number sort will then do

1
2
...
10

and your alpha sort will fix the

1.A
2.B
...
10.A

part

To get the NUMERIC part of the text guy, use the VAL function,

Val("10.A&quot returns "10" as a NUMBER

and to get the ALPHA part, you'll need to use a combo function to find where the "." is, and grab the character(s) to the right of it:

CharPart = Mid("10.A", Instr("10.A", ".&quot+1)

will return "A"

Put these expressions in a query, and you'll have the two parts that you'll need to do a proper sorting/grouping in your report.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Guest_imported]

If you're not going to be adding random other alpha numeric stuff half way through, you could create another field called "Order" in your tables, and just write 1,2,3,4,... alongside the relevant record in their correct order... If you've got the time that is, and if you didn't understand a word of what 'WildHare' had to say. I would explain it to you, if I had the time. But I don't.

 

[NewBie12345]

Thanks WildHare!!!

What I did was:

Create a second field that striped the number out using the "val" function, Sort = Ascending (this is placed before the original field in SQL syntax)

Then I sorted the original field ascending and ... all is well in the world!!!

Many Thanks
Bill
hodgie75@aol.com