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

--

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



"Billy B" <(E-Mail Removed)> wrote in message
news:CAE7B909-4FA4-4474-9F2B-(E-Mail Removed)...
>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;"

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:CAE7B909-4FA4-4474-9F2B-(E-Mail Removed)...
> >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;"
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:032BDD51-7650-4414-B119-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:CAE7B909-4FA4-4474-9F2B-(E-Mail Removed)...
>> >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;"
>>
>>
>> .
>>