By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

Putting data in order

P: 76
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
Oct 24 '06 #1
Share this Question
Share on Google+
10 Replies


P: 4
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
Oct 24 '06 #2

Andrew Thackray
P: 76
Are you trying to get the alpha part in text order and the numeric part in number order ?
Oct 24 '06 #3

Andrew Thackray
P: 76
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)
Oct 24 '06 #4

Expert 5K+
P: 8,434
...
Expand|Select|Wrap|Line Numbers
  1. select left(testdata,3) as Al,right(testdata,len(testdata)-3) as nu
  2. from Table1
  3. 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?
Oct 24 '06 #5

Andrew Thackray
P: 76
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.
Oct 25 '06 #6

Expert 5K+
P: 8,434
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.
Oct 25 '06 #7

NeoPa
Expert Mod 15k+
P: 31,426
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.
Oct 25 '06 #8

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

Charlie
Oct 26 '06 #9

Andrew Thackray
P: 76
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
Oct 26 '06 #10

NeoPa
Expert Mod 15k+
P: 31,426
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.
Oct 26 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.