Listbox Order by not sequntial

[Billy B]

I have the following c ode in the OnLoad event of a form which generates the
numbers I need in an unbound control. The problem is the sequence order
being displayed (10, 11, 14, 159, 21, 211, 30). Is there any way I do to
correct the problem?

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE Code <>
'""' ORDER BY Code ASC;"

 

[Ken Snell]

ACCESS is seeing your numbers as text, so it's sorting using text rules and
not number rules. Try this:

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE Code <>
'""' ORDER BY Val(Code) ASC;"

 

[Billy B]

Thanks Ken. When I tried this but no results are showing in the listbox. I
copied your code and pasted it in the ON Load event but I'm not getting
anything.

ACCESS is seeing your numbers as text, so it's sorting using text rules and
not number rules. Try this:

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE Code <>
'""' ORDER BY Val(Code) ASC;"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have the following c ode in the OnLoad event of a form which generates
the
numbers I need in an unbound control. The problem is the sequence order
being displayed (10, 11, 14, 159, 21, 211, 30). Is there any way I do to
correct the problem?

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE Code
<>
'""' ORDER BY Code ASC;"

.

 

[Ken Snell]

Probably a linewrapping problem? All I did was add the Val function as a
wrapper to your Code field in the ORDER BY clause. Go back to what you had
originally, then add the Val function as I did to your code.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Thanks Ken. When I tried this but no results are showing in the listbox. I
copied your code and pasted it in the ON Load event but I'm not getting
anything.

ACCESS is seeing your numbers as text, so it's sorting using text rules
and
not number rules. Try this:

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE Code
<>
'""' ORDER BY Val(Code) ASC;"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have the following c ode in the OnLoad event of a form which generates
the
numbers I need in an unbound control. The problem is the sequence order
being displayed (10, 11, 14, 159, 21, 211, 30). Is there any way I do
to
correct the problem?

Me.lstCode.RowSource = "SELECT DISTINCT Code FROM [PO Master] WHERE
Code
<>
'""' ORDER BY Code ASC;"

.