Putting data in order

[ckpoll2]

Hello,

I have a report that returns document numbers in the format "AA999999". These numbers are not in alphabetical and then numerical order. How can I put these in order?

Thanks,

Charlie

[ashatzen]

Hi Charlie. Create a query that sorts the data in the order you want. Then set the Record Source of the Report to be the query, instead of the table.
Amy

[Andrew Thackray]

Are you trying to get the alpha part in text order and the numeric part in number order ?

[Andrew Thackray]

If you want the fields in alpha then numeric order you can do it in a query. To test this I created a table "Table1" with one text field "Testdata" where I put the data in the form
XXXnnnnnn where the alpha part was 3 characters and the numeric could be any length. the following SQL query orders the data in alpha then numeric order

select left(testdata,3) as Al,right(testdata,len(testdata)-3) as nu from Table1 order by left(testdata,3),right(testdata,len(testdata)-3)

[Killer42]

...

select left(testdata,3) as Al,right(testdata,len(testdata)-3) as nu
from Table1
order by left(testdata,3),right(testdata,len(testdata)-3)

Hi.
I'm curious. In this kind of SQL statement, could you instead say order by Al, nu?

[Andrew Thackray]

Hi.
I'm curious. In this kind of SQL statement, could you instead say order by Al, nu?

No, I tried that & got an error. I think it is because the SQL compiler evaluates the order by clause before it allocates field aliases.

[Killer42]

No, I tried that & got an error. I think it is because the SQL compiler evaluates the order by clause before it allocates field aliases.

Sounds reasonable.

[NeoPa]

Hi,

I hope I'm not missing the point here, but try going to the design of the report and clicking on 'Sorting and Grouping'. When the popup comes up, add the 'Document Number' field in as the main (only?) sort field.
You won't need to enable Group Headers or Footers.

[ckpoll2]

Great. I thank you all for your help. My report looks great now.

Charlie

[Andrew Thackray]

Hi,

I hope I'm not missing the point here, but try going to the design of the report and clicking on 'Sorting and Grouping'. When the popup comes up, add the 'Document Number' field in as the main (only?) sort field.
You won't need to enable Group Headers or Footers.

The issue is that when mixed alphanumeric fields are sorted they are sorted in srtict alpha order hece a field containing

a123
a20
a300
a5
a10000

will sort as

a1000
a123
a20
a300
a5

when what you really want is

a5
a20
a123
a300
a10000

ie the number part sorted in numeric sequence

[NeoPa]

I have a report that returns document numbers in the format "AA999999". These numbers are not in alphabetical and then numerical order. How can I put these in order?

But, from the original post, we know that the layout is fixed length.
So, while you make a very valid point, it doesn't pertain here.