Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox sort (Access 2000)

    I have a listbox that fills with the following
    SELECT [tblSamples].[SampleNumber] FROM tblSamples ORDER BY [tblSamples].[SampleNumber];
    The sample number format is GO-2005-1000, GO-2006-2990 and etc.
    Problem is with GO-2005-52 comes after GO-2005-505.

    Is there an easy way to fix this.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox sort (Access 2000)

    The values are seen as text, and hence sorted alphabetically, not as numbers.

    Create a query based on tblSamples.
    Add the SampleNumber field and the following calculated columns:
    <code>
    P1: InStr([SampleNumber],"-")

    P2: InStr([P1]+1,[SampleNumber],"-")

    LeftPart: Left([SampleNumber],[P1]-1)

    MiddlePart: Val(Mid([SampleNumber],[P1]+1,[P2]-[P1]-1))

    RightPart: Val(Mid([SampleNumber],[P2]+1))
    </code>
    Save this query as qrySamples.

    Next, create a new query based on qrySamples. Add SampleNumber, LeftPart, MiddlePart and RightPart to the query grid (in this order). Sort on LeftPart, MiddlePart and RightPart.
    Save this query as qrySortedSamples and use it as Row Source for your list box.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox sort (Access 2000)

    Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •