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. 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:
    377
    Robert Vivian
    Aug 1, 2003
  2. 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:
    176
    F. N'Jie
    Sep 8, 2003
  3. Guest

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

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

    record order field function , like tab order function

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

    Copy from listbox to listbox

    Guest, Sep 6, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    193
    Guest
    Sep 6, 2007
Loading...

Share This Page