Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    4

    Lightbulb Unanswered: sorting on second digit

    Can any one help me with the means to sort records on the second letter or number in a text field in Access 2007?

  2. #2
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    in a query, you'd have to write an expression that extracts the 2nd character of a string, then you can sort on that character.

    Example:
    If I have a City field and I want to sort on the 2nd character in that field, here's my query (in access query design view)

    2ndCharacter: MID([City],2,1)

    MID returns a certain number of characters starting at a defined point for a defined string. MID(<AccessField>,<StartingPosition>,<NumberOfChar actersReturned>)

    From there you can set a sort on that field

  3. #3
    Join Date
    May 2009
    Posts
    4
    You will conclude that I am a complete idiot. I used the syntax you suggested but probably put it in the wrong place. I used the sort line of the field on the query but got the message " the text you entered isn't an item on the list". What am I doing that is wrong?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to put 2ndCharacter: MID([City],2,1) in the TOP line in a blank column (where you normally put a field name - this is called an expression). You'll then see the results under the heading: 2ndCharacter when you run the query. You can then set the sorting method under this column going back into the query design.
    Last edited by pkstormy; 05-22-09 at 01:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2009
    Posts
    4
    Bless you!!!! At first I muddled because I had not phrased my question accurately. When I did finally apply my tiny mind to what you told me I was able to work it out and get exactly what I needed, that is, a complete list sorted on the second and third digits of my cownum field. What I have got Access do is to list records in order of the individual cow id ignoring the year number, using the eg, 445/69 would follow 843/80 regardless or year number. Thank-you very much for your help.
    Last edited by VPIRI; 05-22-09 at 09:42.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Yes you are making sense to get 445/69 and 843/80 change Mid([COWNUM],2,1) to Mid([COWNUM],2,6) where 6 is the numbers of characters that you want including the first character.

  7. #7
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    I think so. What you actually need access to do is to ignore character #1 and sort on characters 2 and 3? Do you ever have more than 100 cows (i.e. do you ever have a cow ID 4101/55)?

    If you don't, then you can change your query to this: MID([COWNUM],2,2) and then your sorting should be correct.

  8. #8
    Join Date
    May 2009
    Posts
    4
    I see what you mean, - I would get a clearer list of available cownums from 1 to 99 if I just used #,2,2#. However in the past we had over 100 cattle and used a system of coloured tags which are still coded into our numbers so I need the 6 characters to avoid replication. Thanks very much for your clear explanations.

Posting Permissions

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