sorting info on a form

[orshims]

hi guys

i have a form with a combo box. The combo box information comes from a query, in the query i have sorted the information in the query to ascending which works fine (the information is a list of drill sizes for example 0.5mm 0.6mm etc up to very large sizes).
the issue is when i want to select a specific size of drill from the list i find that its sorted in ascending order but it jumps from 1.9mm to 10mm. the way i would expect it to work is to see 2.0mm next and so on.

can someone please advise me if there is a way i can get around the problem

thanks in advance

 

[Beetle]

Your data is text, which is sorted by characters, so 10 (or 100 or 1000, etc.) will always come before 2. If all your sizing is in mm, then you might want to remove the "mm" and just store the values as numbers (using the Single or Double data type), then it wil sort correctly. The "mm" can just be displayed wherever needed.

If for some reason you can't store the values as numbers, then you'll need a little massaging to get the sort order correct. For example you might add a hidden column to your combo box Row Source query that extracts just the numeric portion from the value and sort by that.

Post back if you need more help with any specifics.

 

[orshims]

hi
thanks for your reply. sorry i am not too confident with access i have picked this up from somebody who did it before and trying to understand.
can you advise me that an idiot could understand, if you need me to post the form or table or query name then i will do so, as i dont understand the last bit

thanks again


simon

 

[Beetle]

Are all of your sizes in mm?

 

[orshims]

yes the sizes go from 0.4mm all the way to 50mm

 

[Beetle]

To solve your problem for this one combo box, you can do the following;

Open your form in design view and open the properties sheet for the combo box. Open design view for the query in the Row Source of your combo box and add a calculated field that looks like this (replace [YourField] with the actual name of the appropriate field);

SortOrder: CDbl(Left([YourField],Len([YourField])-2))

Sort this field Ascending and uncheck the Show box. If you have similar combo boxes anywhere else you will have to repeat this process for them.

Personally, I would probably remove the "mm" from the table values and change the data type to Number with a field size of Double (or Currency). That way it will sort correctly wherever you are using it without the extra work.

 

[orshims]

thank you for your help thats great