How to Get Students' Results in Ordinal Form (1st, 2nd, 3rd etc)

[musaz65]

How can i create 1st, 2nd, 3rd e.t.c numbers in Microsoft access when my numbers are appearing in text boxes ?

[Luuk]

Someone solved this problem for you a while a go, and mentioned:
"Hope it's useful for someone.!"

i also post his code here:

Public  Function IntToOrdinalString(MyNumber As Integer) As String
    Dim sOutput As String
    Dim iUnit As Integer
    
    
    iUnit = MyNumber Mod 10
    sOutput = ""
    
    Select Case MyNumber
        Case Is < 0
            sOutput = ""
        Case 10 To 19
            sOutput = "th"
        Case Else
            Select Case iUnit
                Case 0 'Zeroth only has a meaning when counts start with zero, which happens in a mathematical or computer science context.
                    sOutput = "th"
                Case 1
                    sOutput = "st"
                Case 2
                    sOutput = "nd"
                Case 3
                    sOutput = "rd"
                Case 4 To 9
                    sOutput = "th"
            End Select
    End Select
    IntToOrdinalString = CStr(MyNumber) & sOutput
End Function

[Narender Sagar]

If you already have numbers (1, 2, 3..) in a column, then a simple solution can be.. just create a separate table wherein you can maintain two fields such as 'Number' and 'Ordinal Text' (numbers and their ordinal form-like 1st, 2nd etc.). Simply join these two tables in a query and you can get the desired result wherever you want.

[Luuk]

A user-defined function would be better to get this job donem because it's very static data we are taling about.

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

[NeoPa]

Interesting solution by Luuk. I agree that's a far better way to handle numbers whose limits you don't know (How big would the table need to be to handle an infinite set of integers??)

While the code is also quite elegant it might be improved :

Private Const conSuffix As String = "stndrdthththththth"

Public Function OrdinalSuffix(ByVal lngVal As Long) As String
    lngVal = Abs(lngVal) Mod 100
    If (lngVal = 0) _
    Or (lngVal Between 4 And 20) Then
        OrdinalSuffix = "th"
    Else
        OrdinalSuffix = Mid(conSuffix, (lngVal Mod 10) * 2 - 1, 2)
    End If
End Function

[Luuk]

I had to mention the fact that the code was found on the internet (^1).
But some guys change my posts and the delete the link... ;)

Removed link again. Link was to assign credit to Chip Pearson where the original code was found.

[NeoPa]

Yes Luuk.

That guy was me and I've done it again as it's against the rules of the site to post a link to competing resources. It's called leeching and you'll find that's illegal on most forum sites you visit. As one of the moderators it's my responsibility to remove such links as and when I find them and direct the member posting them so they understand not to repeat the behaviour in future.

This is no reflection on Chip Pearson who provides a lot of good resources for Access, but I think most intelligent people will understand why leeching is unhelpful for a forum site to be able to do what it does - which ultimately is to help those who need help with Access.

Nevertheless, it was an example of some clever code and you did well to select and post it. We can all see that you attributed credit where it was due.