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

Specific Sorting Requirements

P: 21
Greetings all!

I am attempting to complete a sort option on a report. I want to be able to show all records in ascending order of each subset of categories. In other words, I will have different "Locations" where items are going to be stored on shelves. They are labeled by the alphabet- A, B, C, D, E and so on. However I have "sub levels" such as A1, A2, A3, A13, A64 and so on.

The problem I am running into in the report is it is listing the categories and it's sub levels as A1, A10, A11, A12, A13, not like I want them to- A1, A2, A3...A10, A11,...A20

Is there a workaround for this problem? Any help would be greatly appreciated!!

Much thanks!
Adam
Feb 21 '08 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
The problem I am running into in the report is it is listing the categories and it's sub levels as A1, A10, A11, A12, A13, not like I want them to- A1, A2, A3...A10, A11,...A20
Hi Adam. The sort is working correctly, for alpha characters (A10 is indeed a lower sort value than A2 in terms of character ordering) - after all, the sublevel is not a number, but a text string.
If the sublevel number is already available directly as a number in your base query you need to sort the report on the overall category then the level number separately.
I suspect the sublevel number isn't available in your base query or you would already have tried this. If this is so add a calculated field to the underlying query which extracts the level number as a number (substituting the name of the actual field for [SubLevel Field Name] below):
Expand|Select|Wrap|Line Numbers
  1.  
  2. LevelNo: Val(Right$([SubLevel Field Name], Len([SubLevel Field Name])-1))
  3.  
In your report's sorting you will then need to sort on the category then on LevelNo to get the sorting as you want it to be.

-Stewart
Feb 21 '08 #2

P: 59
the problem might lie with the way access is interpreting your sublevels, as Stewart pointed out. there is prob a smarter way to do it...
Feb 21 '08 #3

NeoPa
Expert Mod 15k+
P: 31,768
Another way would be to format the sublevel name to include zeroes to the number of places of the maximum value.
Say your sublevels go from A1 to A69, B1 to B69, etc. You could then store them as A01 to A69, B01 to B69, etc.
Obviously, if the maximum value were 720 instead of 69 then you'd need three digits for the number - A001 to A720, etc.
Feb 22 '08 #4

P: 21
Thanks everyone for your help...I chose NeoPa's suggestion on just adding a zero before the numbers. That was the easiest fix...so common sense.

Thanks again!
Feb 25 '08 #5

NeoPa
Expert Mod 15k+
P: 31,768
Our pleasure :)
All ideas help in that they get the brain going. I like to store them away for other situations in future.
Feb 25 '08 #6

Post your reply

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