Results 1 to 6 of 6
  1. #1
    neil40 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    4

    Sorting by position in race

    Hi I have a query that I want to sort by


    a) Event number
    b) Finishing postion - results are denoted 1st, 2nd, 3rd etc.

    However, when I do this, I get
    10th
    1st
    2nd
    etc.

    Is it possible to change this to sort:
    1st
    2nd
    3rd
    .
    .
    10th

    Many thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't use ordinals, just numbers in a number field. Otherwise, use VAL function to extract number.

    SELECT * FROM table ORDER BY Event, Val(Position):
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sort where?
    Maybe if you use Val function, thus you'd get only the numeric portion on the left side of the string. If this is a query, then add a calculated field and uncheck the show box if you don't want to see it. Something like Position: Val(myPositionFieldNameHere) and use the sort ascending option in the query design grid. If it a table, then you'll need to add a sort field, but I would not worry about sorting in a table since you shouldn't be playing around in them anyway.

    Late to the party again...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    neil40 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    4
    Quote Originally Posted by Micron View Post
    Sort where?
    Sorry, "I" thought that as I posted in the query section it was obvious, as there are sub forums for various Access aspects.
    My apologies

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are quite right. So few people post here in the optimal forum that I often don't look anymore. My bad. I just wanted to be sure you didn't want to sort the table.
    Very common to find posts that are definitely Word/Excel/Outlook questions but are posted in Access related forums, or everything gets lumped into "Access" even when it's query/code related.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Another option is to just store the number and add the suffix for display

    Code:
    Public Function getNth(vInt As Integer) As String
    
        Dim Nth As String
    
        On Error GoTo getNth_Error
    
        Select Case vInt
        
            Case 1, 21, 31
                Nth = vInt & "st"
            Case 2, 22
                Nth = vInt & "nd"
            Case 3, 23
                Nth = vInt & "rd"
            Case Else
                Nth = vInt & "th"
    
    End Select
    
        getNth = Nth
    
    On Error GoTo 0
        Exit Function
    
    getNth_Error:
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getNth"
    
    End Function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 11-26-2014, 06:51 AM
  2. Race car scaling (complicated)
    By BEI77 in forum Programming
    Replies: 1
    Last Post: 07-28-2014, 03:37 PM
  3. Handicap Race System.
    By Mantaii in forum Reports
    Replies: 4
    Last Post: 01-16-2012, 07:56 AM
  4. Calculating race finishing positions
    By Coheeba in forum Queries
    Replies: 4
    Last Post: 09-01-2011, 12:44 PM
  5. Race Sports Time format
    By flebber in forum Database Design
    Replies: 0
    Last Post: 09-13-2009, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums