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
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
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.
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.
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.
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