Sorting numerically

[barrowfordred]

i have a simple datsbase with 2 fields - drawing number and description.
the drawing numbers are in the formatt STD123 e.t.c.
When I try to sort numerically the order ends up as
STD1
STD10
STD11
STD100
STD101..........
How do I get it to list in true numeric order in report and table view i.e.
STD1
STD2
STD3.......... Please bear in mind that my knowledge of Access is VERY limited. I've tried a search and don't understand about queries and such like. Even so I'd have thought that even the most basic database programmes would be able to handle something as simple as an alpha/numeric sort list....seems not!!!!!
????????????????

 

[neileg]

No database is capable of intuitively knowing that there is some kind of struture within the data in a field. If the structure is important to you, you need to build your database to reflect it.

The best way to deal with this is to split your drawing number into two parts, the alpha and the numeric. Then it will sort as you want.

If the drawing number is always in the format of 3 alphas followed by just numbers, it is quite simple to split this up in a query and apply the sort to the query.

 

[barrowfordred]

If the drawing number is always in the format of 3 alphas followed by just numbers, it is quite simple to split this up in a query and apply the sort to the query.

I'll give this a go, although I haven't the faintest idea how to start. The drawing number format is - STD.123 USA
with the "USA" being only present on a few numbers.

 

[alastair69]

If you want to post your Database here i will inclose a query that will split your data for you.

Alastair

 

[barrowfordred]

Thanks for that - zip file attached. This will then give me an idea as to change any future databases.

 

[alastair69]

You will be asked to input "intSegment1" & "intSegment2", just press the following buttons:
intSegment1 = 1
intSegment2 = 2

This will allow you to sort the colums in question, i hope this will help you in your quest and good luck.

 

[barrowfordred]

Thanks for that, I'll give it a go.

 

[IgorB]

You are lucky that you have first 3 characters as alpha(string).
It is easy to select first 3 as 1 field, and rest of it as numbers then you sort field by alpha and numbers.
But if you have inconsistant data you would need to write 2 functions to purge all alpha characters and then purge numbers.
In this case you'll dealing with a loop examines each character in field and purges one by one and concatinate in 1 string/number.

 

[Jon K]

Since there is a mix of alphanumerics after the 6th digit (except for the number "STD MK1 Foam", which is all text), I think the best that can be done is to ORDER BY the first 4 digits as text, then a number (which is of 2 or 3 digits), and then text again after the 6th digit.

SELECT [STD Drawing Number], [Description]
FROM [STD Drawing Numbers]
ORDER BY Left([STD Drawing Number],4), Val(Mid([STD Drawing Number],5)), Mid([STD Drawing Number],7);
.