Sort Numerical Values in Combobox (1 Viewer)

[theDBguy]

I understand that but we are constantly adding new products. We currently have 800 products and it will be ever increasing. How are we meant to keep them in order.

Hi. Either the computer can do the sorting automatically for you, or you tell it exactly what sort order you want. Computers can only sort either numerically or alphabetically.

So, since you use a Text field, the computer uses alphabetical sorting. But since you want numeric sorting, you'll have to use a Number field. To do that, you either add a numeric field for sorting, or you split your text data into its numeric and character data.

 

[Pat Hartman]

I tried that but I still have text values in the field. We have a mixture of the two. I just need it to sort the numerical values by value and ignore the text values.

ONLY numeric data types sort like numbers. Text data types sort like text. Therefore, if your field includes text characters, it will sort like a string because it IS A STRING. Strings sort character by character left to right and since 2 is > 1, 2 will sort AFTER 100000000, period.

Count me with the others. If you want to keep the strings, then add a pure numeric value on which to sort and don't forget to maintain it when you add new rows or change existing rows.

OR, if you can separate the string into prefix-number-suffix, then you can get the sort to be numeric on the number field if that is what you want. Otherwise, M5 is always going to sort after M45 and will always test as greater than.

 

[MajP]

Could you export a some records from?
"SELECT DISTINCT Detail, DetailID FROM TblDetail "
Make it a large set and post.
What is the datatype of Detail?

 

[The_Doc_Man]

You have a design issue based on trying to sort non-comparable identifiers. It will be a permanent pain in the patootie until you step back to review and redesign this feature. Using so many mixtures of size descriptions as are you are using, it will be impossible to straighten out and still DIRECTLY use these measurements. However, there is always a back door.

Consider a table that you can use that identifies sizing info. The key to the table is the raw sizing info. It might be "1mm" or "1.75in" or "M12" - whatever sizing designation you use in the actual product. But have a second field in that table that holds a conversion of EVERY one of those indicators to a uniform numeric sizing scale in whatever units you want to use.

I don't know your units, but lets say for argument's sake that you had millimeters as your preferred "master" sizing unit. So the first entry would be <"1mm", 1.00000>. The next would be <"1.75in", 44.4500> Then you might have <"M12", 35.0000>. In other words, a master size conversion table to bring everything to uniform units. I would be surprised if you DIDN'T already have something like this anyway.

The next step would be a query to JOIN the size of your product to this sizing table to find the uniform size. Then sort by THAT quantity. At that point, you don't care if you have 800 new products as long as most of them have sizes that are already in, or could be added to, your sizes table. Then you only have to worry about differences between "1mm" and "1 mm" - which could be handled either by removing all spaces from the product size field or by making two entries in the size lookup table to match the different sizing nomenclature with different spaces.