SQL ORDER BY clause

Alpha fields like 10-A are always sorted alpha.
Set up a calculated sorting field like this:

Sorter:Left([Product_VAMID],Len([Product_VAMID])-2)

Thanks for your answer DKLipper. Your answer only works if you add CAST:
CAST(Left([Product_VAMID],Len([Product_VAMID])-2) AS NUMERIC)
I am looking for a solution other than this one. I will wait to see If someone out there knows a different answer. If not I will close the question. I will still give you the points though, thanks a lot!

Did you try this:

SELECT Product_VAMID FROM Product ORDER BY Product_VAMID ASC

or

SELECT Product_VAMID FROM Product ORDER BY Product_VAMID DESC

Try it out.
Thanks.

Let us assume fields Product_VAMID has a field length 8. Then the query would be

select convert(num(8),Product_VAMID) as newfield from Product order by newField

This query should work.

All the best
Raja Venkatesh
+971-50-5688186

It may be too late to do this, but if you zero fill the number parts of the product IDs so they are all the same length, sorting will work properly. Based on your examples, the product numbers are a maximum of 4 characters, so that instead of "11-a", you should have "0011-a".

If all of your product IDs include a dash, then the easy way to do this is to create an SQL update query that will find the location of the "-" and, based on it's location, add an appropriate number of "0" (zeroes) to the front part of the ID.

Then make sure that all new data entered formats the product IDs with zero fill at the front of the number part of the ID so the number is always 4 characters long (or 5, if you need to be able to handle larger numbers).
The basic rule is that when sorting any kind of text you must place all characters in the same position, so that if part of the text is a number, the tens digit must be in the same physical location for every text.

Even if this involves some work at this point in time, you might still be better off making the change now to avoid further problems in the future.