Cascading combo box - sort text numerically

[motormouth1991]

I hope some one is able to help me with this one.


I have a database which is used to search for products by its specifications, i have set up cascading combo boxes to work in the following order.

Profile
Thickness
Width
etc.

So the current row source of one of the Combo boxes is
SELECT DISTINCT [tbltestv2].[Thickness] FROM tbltestv2 WHERE ((([tbltestv2].[profile])=(Forms]![Run ratev2]!cboprofile]))
This basically means that it will search for whatever thicknesses are available in the selected profile.
This is the one that works fine although it is not sorted numerically.
I.e 1, 1.2, 12, 2, 2.5 where I want it as 1, 1.2, 2, 2.5, 12

From here I amended the order by so that it will sort it numerically, In the row source I had the following expression.
SELECT [tbltestv2].[Thickness] FROM tbltestv2 WHERE ((([tbltestv2].[Profile])=[Forms]![run ratev2]![cboprofile])) ORDER BY Val([Thickness]), IIf(Val(Right$([Thickness],1))=0,Right$([Thickness],1),"");
The issue with this one is that it shows duplicate data for instance 1, 1, 1, 1.2, 1.2, 2.5, 2.5, 12, 12, 12
I know that if the beginning of the expression is SELECT DISTINCT it will eliminate this however, the SELECT DISTINCT is conflicting with the ORDER BY and it will not let me do it.

Im not sure whether i have missed out something small and obvious but it has been irritating me for a few days now.
Thank you in advance to anyone who can shed any light on this for me!

[ssanfu]

There are two types of characters: text and numeric. If the thickness field in the table is defined as TEXT, it sorts according to alphabetic rules, ie from left to right.

Is it necessary to have the thickness field as TEXT?
If you change the field type to NUMBER, you will need to also select "Single" or "Double" to be able to have decimals.

I would change the thickness field type to NUMBER and use the Str() function to convert the number to a string.

[motormouth1991]

I would change the thickness field type to NUMBER and use the Str() function to convert the number to a string.

Could you elaborate on how the str() function would work? I would need the field to be text as some of the information is displayed as '5/9' therefore would need to be a text field.

[motormouth1991]

I have figured out the formula now, just incase anyone comes across this in the future the following needs to be in the row source

SELECT [tbltestv2].[Thickness] FROM tbltestv2 WHERE ((([tbltestv2].[Profile])=[Forms]![run ratev2]![cboprofile])) GROUP BY [tbltestv2].[Thickness] ORDER BY Val([Thickness]), IIf(Val(Right$([Thickness],1))=0,Right$([Thickness],1),"");

The thickness field needs to be set as text and this will allow a cascading drop down menu to sort information numerically whilst still displaying string as long as all fields are filled in.

[Rawb]

How are the dimensions actually stored in the db? Are they stored as fractions or as decimals?

If they're stored as fractions, you might want to look at changing them all to decimals and then just converting them to fractions when you display it on the Forms, Reports, etc.

Or, you could figure out a way to convert them from fractions to decimal for sorting.