Sorting 0-9 then 10-13

[lukestkd]

Hello all,
I am making a database to store homework set within our school department. I want to run a query that feeds a report showing records sorted by class.

Class codes range from 7x to 13x, access defaults to ordering this 10,11,12,13,7,8,9. Where I require 7,8,9,10,11,12,13.

Can this be done?

Many Thanks in advance!

Luke

 

[plog]

Make your numeric data, numeric. The data you have is being orderd by text ordering rules because you have stored it as text. You need to make the fields that hold that data numeric and then it will order it according to numeric ordering rules.

 

[lukestkd]

Thanks Plog.

I've tried this, however our class codes are number letter letter letter number e.g. 9xIt2, and the number type won't let me enter it as this.

 

[plog]

I would then break this class code up into new fields for all the distinct elements it contains. Obviously the first number is a distinct data element so it should become its own field. I don't what else this code is telling you, but for however many parts it has, it should have that many fields.

What does each character of this code tell you?

 

[Isskint]

Add a second field SortOrder which will be numeric only. I use this on a delivery paperwork databse where it is not unuusal to get a drop 3A or 5B etc. My DropSort uses decimals so drop 3 is 3.0 and 3A is 3.1 etc. This way you sort on the SortOrder.

 

[gemma-the-husky]

as long as they start with a number, you can use val(formname) to get the number

sort by 2 columns - the val, and the full name, and it should work