Specific Sorting Requirements

[awojciehowski]

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

[Stewart Ross]

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):

 
LevelNo: Val(Right$([SubLevel Field Name], Len([SubLevel Field Name])-1))
 

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

[blad3runn69]

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...

[NeoPa]

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.

[awojciehowski]

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!

[NeoPa]

Our pleasure :)
All ideas help in that they get the brain going. I like to store them away for other situations in future.