Sort Numerical Values in Combobox

[Rob_Jones101]

Hi. I have a combobox with different sizes of products like metal sheets and bars with sizes like 1mm up to 1500mm. The combobox is organising them like the photo below. The comboboxes are cascading and work fine but wont organise into numerical value.

Is there a way that you can organise it by numerical value.

In the table they are set as numbers.

Thanks in advance.

 

[theDBguy]

You can add a numeric column in your Row Source for sorting

 

[Rob_Jones101]

You can add a numeric column in your Row Source for sorting

They are all sorted in the row source which is why I am confused. All the other combo boxes are sorted but any with numbers arent sorting like they should.

 

[MajP]

What @theDBguy is saying, your source table would include a numeric sort field
tblChoices tblChoices

SortOrder	Size
1​	1mm
3​	10mm
4​	100mm
2​	2mm

If you sort on sort order
tblChoices tblChoices

SortOrder	Size
1​	1mm
2​	2mm
3​	10mm
4​	100mm

 

[Gasman]

You could try VAL(fieldname) ?

 

[MajP]

Val will work on the list on the right but not the list on the left

The Val function stops reading the string at the first character that it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized.

Query1 Query1

Size	TheVal
1mm	1​
10mm	10​
100mm	100​
2mm	2​
M12	0​
M16	0​
M10	0​

 

[Minty]

When you say these are stored as numbers I'm confused.
Are you concatenating the values to create the combo lists.

And you would need to differentiate between metric and imperial measurements if I'm not mistaken.
So exactly how is the data stored?

 

[Gasman]

Val will work on the list on the right but not the list on the left

Query1 Query1

Size	TheVal
1mm	1​
10mm	10​
100mm	100​
2mm	2​
M12	0​
M16	0​
M10	0​

Yes, I realise that, I thought that was the only one the o/p was looking at, as he said length?
Why the units are not separate is beyond me ?

 

[Pat Hartman]

Strings sort character by character, left to right so 10" will ALWAYS sort before 2" because 1 is < 2. To solve the problem, you need to store the data correctly as two fields. A number and the UOM separately. You sort on the numeric value and display the concatenated number & UOM. HOWEVER, if you want the list to be ordered rationally so that MM are sorted where they logically belong in the list of inches, you need to convert everything to a single UOM. You can still display the list however you want but the internal value will control the sort order.

 

[Rob_Jones101]

I have tried the suggestions but nothing is working. I have separated the UOM but it still wont sort numerically. I have tried doing the sort order but hasnt worked either. I have added a space between number and UOM as well. Any other suggestions?

 

[Minty]

What is the SQL for your combobox row source?
The suggestions made will definitely work if implemented correctly.

 

[MajP]

Now that you seperated change the value field from short text to numeric.

 

[Rob_Jones101]

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.

 

[MajP]

Post rowsource and the data types of the fields.

 

[Rob_Jones101]

SELECT DISTINCT TblDetail.Detail, TblDetail.DetailID FROM TblDetail; There is no sort on them as it is in the Code.

Me.Cbodetail.RowSource = "SELECT DISTINCT Detail, DetailID " & _
"FROM TblDetail " & _
"WHERE ProductID = '" & Me.Cboproduct.Column(1) & "' " & _
"ORDER BY Detail"

DetailID is number and Detail is short text. If i have it as a number it wont allow me to enter them.

 

[theDBguy]

SELECT DISTINCT TblDetail.Detail, TblDetail.DetailID FROM TblDetail; There is no sort on them as it is in the Code.

Me.Cbodetail.RowSource = "SELECT DISTINCT Detail, DetailID " & _
"FROM TblDetail " & _
"WHERE ProductID = '" & Me.Cboproduct.Column(1) & "' " & _
"ORDER BY Detail"

DetailID is number and Detail is short text. If i have it as a number it wont allow me to enter them.

Hi. As @Gasman suggested earlier, have you tried?

Me.Cbodetail.RowSource = "SELECT DISTINCT Detail, DetailID " & _
       "FROM TblDetail " & _
       "WHERE ProductID = '" & Me.Cboproduct.Column(1) & "' " & _
       "ORDER BY Val(Detail)"

 

[The_Doc_Man]

The earlier suggestion from theDBguy (his post #2, and now also from me) is that you add a column of numbers that represent the order in which you wish to see these records. Leave gaps between the numbers you assign. Then in the .RowSource you can use an ORDER BY of this numeric column that doesn't have to actually be part of the combo box's returned values. If you need to insert a few new rows, leaving gaps between the original numbers might help you insert records in your desired order without having to re-do the numbers all of the time.

As long as you have text in the fields to be sorted, they will sort character-by-character, not value-by-value.

 

[Rob_Jones101]

Nothing appears in the combobox if I change it to Val(Detail)

 

[theDBguy]

Nothing appears in the combobox if I change it to Val(Detail)

Interesting...

 

[Rob_Jones101]

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.