Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Unanswered: Sorting order error

    Hi,

    im sure this must be a common mistake yet I cant find the solution anywhere so forgive me if this has been asked. I just cant find the answer.

    Basically, I have a table and within the table a field called REF

    This field contains a cod for example G007, G009, G095, G103, G1000 and so on so forth.

    now when I query this and try to order this ascending or descending.

    The G1000 is listed before G103. Can anyone explain?

    Also this problem is the same if I take the "G" away.

    Thanks in advance

    jmcall10

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re


    The G1000 is listed before G103. Can anyone explain?
    1st. its sorting is set to string
    so it will compare ,on the 3th character, with eachother and the 0 is les then the 3
    so G1000 wil come before g103
    2nd . cast it to an integer after "deleting" the G, it will work for you
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Aug 2007
    Posts
    10
    Thank you so much for your response.

    I would like to keep the G prefix to be honest.

    Is there no way of changing the 3rd character thing when it sorts?

    Thanks again for your speedy reply

    jmcall10

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The basic problem is that you're sorting a text field so 1000 comes before 2 just as surely as AAAA comes before B.

    The only way around it is to do as Marvels suggests. Make a calculated field that strips the G out, converts the result to be numeric and then sort by this calculated field. You don't have to show this calculated field, just sort by it. That way you will have what you want.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2007
    Posts
    10
    I like this solution

    How do I create this calculated field Stripping out the prefix G?

    Thanks

    jmcall10

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you could try coercing the column to an integer type....
    I don't know if it will work but try say

    order by cint(mid$(<mycolumnname>,2,4)

    if the G applies to all fields then personally I'd get rid of it.. you can always add it back on reports, forms etc......

    NUT you would still have chaneg the datatype of the column to a number to differentiate between 10000 and 11

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Healdem,
    Wouldn't
    Code:
    CInt(Mid$(<col>, 2, Len(<col>)) )
    Be a bit better?
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or, using the Access interface, just put a new column in the query that takes out the first character:

    SortField: CLng(Right([fieldname], Len([fieldname] - 1))

    I like to try to keep things simple wherever possible!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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