Sort Numerical Ascending in Combo Box

[Guest]

I have a combo box:

SELECT DISTINCT Format([IDLength], "#,##0.0000") As IDLen
FROM tblPKCGPhysicalAttributes
ORDER BY Format([IDLength], "#,##0.0000");

This works fine. It's Klatuu's solution for the decimal point problem I was
experiencing. When the combo box was activated it would cut the data down to
2 decimal points and then round up despite the fact that the data was in a
field with a Fixed setting to 4 decimals. Oddly, after making a selection the
number would change to the proper 4 decimals. Perhaps this is some sort of
Access bug. In any case, Klatuu's solution works.

The next problem I have is regarding ascending sort. Right now the numbers
sort like this:

1
10
2
20
3
30

I need them to sort properly:
1
2
3
10
20
30

Anyone have any ideas how this can be done?

Thanks in advance!

 

[Allen Browne]

How about:

SELECT DISTINCT IDLength, Format([IDLength], "#,##0.0000") As IDLen
FROM tblPKCGPhysicalAttributes
ORDER BY IDLength;

Combo properties:
Column Count 2
Column Widths 0

 

[kingston via AccessMonster.com]

Convert the field types from text to numeric via one of many available
conversion functions:

Val(), CInt(), CLng()...

I have a combo box:

SELECT DISTINCT Format([IDLength], "#,##0.0000") As IDLen
FROM tblPKCGPhysicalAttributes
ORDER BY Format([IDLength], "#,##0.0000");

This works fine. It's Klatuu's solution for the decimal point problem I was
experiencing. When the combo box was activated it would cut the data down to
2 decimal points and then round up despite the fact that the data was in a
field with a Fixed setting to 4 decimals. Oddly, after making a selection the
number would change to the proper 4 decimals. Perhaps this is some sort of
Access bug. In any case, Klatuu's solution works.

The next problem I have is regarding ascending sort. Right now the numbers
sort like this:

1
10
2
20
3
30

I need them to sort properly:
1
2
3
10
20
30

Anyone have any ideas how this can be done?

Thanks in advance!

 

[Guest]

Thank you, Mr. Browne! This seems to be working just fine!

--
www.Marzetti.com

How about:

SELECT DISTINCT IDLength, Format([IDLength], "#,##0.0000") As IDLen
FROM tblPKCGPhysicalAttributes
ORDER BY IDLength;

Combo properties:
Column Count 2
Column Widths 0

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a combo box:

SELECT DISTINCT Format([IDLength], "#,##0.0000") As IDLen
FROM tblPKCGPhysicalAttributes
ORDER BY Format([IDLength], "#,##0.0000");

This works fine. It's Klatuu's solution for the decimal point problem I
was
experiencing. When the combo box was activated it would cut the data down
to
2 decimal points and then round up despite the fact that the data was in a
field with a Fixed setting to 4 decimals. Oddly, after making a selection
the
number would change to the proper 4 decimals. Perhaps this is some sort of
Access bug. In any case, Klatuu's solution works.

The next problem I have is regarding ascending sort. Right now the
numbers
sort like this:

1
10
2
20
3
30

I need them to sort properly:
1
2
3
10
20
30

Anyone have any ideas how this can be done?

Thanks in advance!