Listbox Order by not sequntial

Discussion in 'Microsoft Access VBA Modules' started by Billy B, Apr 21, 2010.

  1. Billy B

    Billy B Guest

    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;"
     
    Billy B, Apr 21, 2010
    #1
    1. Advertisements

  2. Billy B

    Ken Snell Guest

    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" <> wrote in message
    news:...
    >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, Apr 21, 2010
    #2
    1. Advertisements

  3. Billy B

    Billy B Guest

    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" <> wrote in message
    > news:...
    > >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;"

    >
    >
    > .
    >
     
    Billy B, Apr 21, 2010
    #3
  4. Billy B

    Ken Snell Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> >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, Apr 21, 2010
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. andreas

    selecting in listbox with vba

    andreas, Jul 1, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    17,711
    Sandra Daigle
    Jul 7, 2003
  2. Robert Vivian

    Add record not showing in listbox first time form is used after form load

    Robert Vivian, Aug 1, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    515
    Robert Vivian
    Aug 1, 2003
  3. F. N'Jie

    How not to select duplicate records in a listbox

    F. N'Jie, Sep 5, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    300
    F. N'Jie
    Sep 8, 2003
  4. Guest

    Multi-select listbox: de-selected record not being deleted

    Guest, Mar 2, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    273
    Nikos Yannacopoulos
    Mar 4, 2005
  5. Guest

    record order field function , like tab order function

    Guest, May 3, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    275
    Guest
    May 3, 2006
  6. Guest

    Copy from listbox to listbox

    Guest, Sep 6, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    328
    Guest
    Sep 6, 2007
  7. jutlaux

    ORDER BY not working

    jutlaux, Apr 15, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    259
    jutlaux
    Apr 15, 2008
  8. Mike

    Not happening in order??

    Mike, Jun 4, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    234
Loading...