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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. andreas

    selecting in listbox with vba

    andreas, Jul 1, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    17,828
    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:
    591
    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:
    360
    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:
    325
    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:
    334
    Guest
    May 3, 2006
  6. Guest

    Copy from listbox to listbox

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

    ORDER BY not working

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

    Not happening in order??

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