Reputation: 31
I have a table with ranks. Now I want the ranks to have the suffixes 'St', 'nd', 'rd', 'th' in Microsoft Access. I want to know if there is a way to make that happen using access query. Thanks for the response in advance
Upvotes: 0
Views: 1011
Reputation: 19857
This code from Chip Pearsons site will do the trick:
Public Function OrdinalSuffix(ByVal Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes
N = Num Mod 100
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
Or ((Abs(N) Mod 10) = 0) Then
OrdinalSuffix = "th"
Else
OrdinalSuffix = Mid(cSfx, _
((Abs(N) Mod 10) * 2) - 1, 2)
End If
End Function
You'd write it into your query as:
SELECT MyField & OrdinalSuffix(MyField)
FROM MyTable
The formula only version is:
IIf(MyField-100*INT(MyField/100)>=10 And MyField-100*INT(MyField/100)<=14,"th",Choose(MyField-10*INT(MyField/10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
Written as
SELECT MyField & IIf(MyField-100*INT(MyField/100)>=10 And MyField-100*INT(MyField/100)<=14,"th",Choose(MyField-10*INT(MyField/10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
FROM MyTable
Upvotes: 1