CODEWITHSUNDEEP
ms-access
billstone09
billstone09

Reputation: 31

Ordinal numbers in Microsoft Access Query

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

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

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

Related Questions