425,830 Members | 682 Online
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

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

 P: 1 How can i create 1st, 2nd, 3rd e.t.c numbers in Microsoft access when my numbers are appearing in text boxes ? Nov 24 '17 #1
6 Replies

 Expert 100+ P: 1,034 Someone solved this problem for you a while a go, and mentioned: "Hope it's useful for someone.!" i also post his code here: Expand|Select|Wrap|Line Numbers 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 Nov 24 '17 #2

 100+ P: 189 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. Dec 5 '17 #3

 Expert 100+ P: 1,034 A user-defined function would be better to get this job donem because it's very static data we are taling about. Expand|Select|Wrap|Line Numbers 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   Dec 5 '17 #4

 Expert Mod 15k+ P: 31,418 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 : Expand|Select|Wrap|Line Numbers 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 Dec 6 '17 #5

 Expert 100+ P: 1,034 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. Dec 6 '17 #6

 Expert Mod 15k+ P: 31,418 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. Dec 7 '17 #7