Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000: Sorting mixed fields into numerical order

    Hi all,

    I'm quite a novice when it comes to Access, so this might be a simple fix.

    I am compiling a small database for cataloguing purposes. I will be using a "RefNo" field which will contain both letters and numbers (e.g. A/1, A/1/1, A/1/2 through to A/1/10) but am unsure how to sort it. I found another thread which sugested the following:

    "Create a query based on the table.
    Add a calculated column IsNumeric([TextField])
    Clear the Show check box and set the sort order to Ascending if you want to sort numbers before text, or to Descending if you want to sort text before numbers.
    Add another calculated column Val([TextField])
    Clear the show check box and set the sort order to Ascending.
    Add the fields from the table, or * if you want to use all fields.
    Use this query as record source for your form."

    I followed these instructions and it did sort them, but decending. No matter what I tried to sort them ascending, it didn't work.

    Does anyone have a solution please?

    Thanks Craig

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    What do you mean by "it did sort them, but descending"? Do you mean it did text before numbers, or Z before A?

    Just so you understand, any sorting of a boolean value (which in your case is the result of "IsNumeric(textfield)") when sorted ascending (the default) will sort True before False because True=-1 and False=0.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply.

    Sorry if I never made myself clear. It sorted them like this:

    A/1/10
    A/1/9
    A/1/8
    A/1/7
    A/1/6
    A/1/5
    A/1/4
    A/1/3
    A/1/2
    A/1/1
    A/1

    That is sorted correcly for my purpose, but the wrong way around. A/1 should be first.

    I am sorry, but like I said, I'm very much a novice when it comes to databases, so that second part of your reply is pretty much meaningless.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    if the field is always like (A/1/1 or 2 digit number) then why not create a calculated column that converts A/1/8 to A/1/08, then sort on that.

  5. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Craig, expanding a little on the solutions offered.

    You are running into a problem with your RefNo field, where it contains mixed types.

    Your field "A/1/10" conceptually contains three different segments of data separated by slashes: The letter "A", the number 1, and the number 10. Access does not see them this way; it sees them as one alphanumeric set of data and does not understand that you have embedded numeric values in your field.

    I'm a little puzzled how you arrived at the sorted order you had above; it should have been in the following order:

    A/1
    A/1/1
    A/1/10
    A/1/2
    A/1/3
    A/1/4
    A/1/5
    A/1/6
    A/1/7
    A/1/8
    A/1/9

    As patt said above, you could fix this by adding a leading zero to the last segment of data, resulting in the following sort order:

    A/1
    A/1/01
    A/1/02
    A/1/03
    A/1/04
    A/1/05
    A/1/06
    A/1/07
    A/1/08
    A/1/09
    A/1/10

    This works until your 2nd set of numbers goes beyond 9 and the 3rd set goes beyond 99. Suppose you added the following to your dataset:

    A/1/20
    A/1/100
    A/2
    A/10

    The order would become worse.

    You could create a calculated column that would add leading zeroes to the numeric segments; but you would have to state ahead of time how large each field could get. The other problem with this approach is that you say you are "quite the novice", and creating the calculated column is probably a skill that you have not attained yet.

    My recommendation is that you either (a) solicit help on creating the calculated column, or (b) in your database split your Refno into 3 separate fields: Refno-1, Refno-2, and Refno-3. Make the last two a type of Number instead of Text. Then to bring everything into the proper order you can sort on all three fields.

    Good luck.
    Last edited by kfreyermuth; 2014-10-02 at 19:43.

Posting Permissions

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