Listbox Order by not sequntial

  • Thread starter Thread starter Billy B
  • Start date Start date
B

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;"
 
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;"
 
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.

Ken Snell said:
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/



Billy B said:
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;"
Click to expand...


.
Click to expand...
 
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/



Billy B said:
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.

Ken Snell said:
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/



Billy B said:
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;"
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top