422,024 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,024 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
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 872
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
  1. Public  Function IntToOrdinalString(MyNumber As Integer) As String
  2.     Dim sOutput As String
  3.     Dim iUnit As Integer
  4.  
  5.  
  6.     iUnit = MyNumber Mod 10
  7.     sOutput = ""
  8.  
  9.     Select Case MyNumber
  10.         Case Is < 0
  11.             sOutput = ""
  12.         Case 10 To 19
  13.             sOutput = "th"
  14.         Case Else
  15.             Select Case iUnit
  16.                 Case 0 'Zeroth only has a meaning when counts start with zero, which happens in a mathematical or computer science context.
  17.                     sOutput = "th"
  18.                 Case 1
  19.                     sOutput = "st"
  20.                 Case 2
  21.                     sOutput = "nd"
  22.                 Case 3
  23.                     sOutput = "rd"
  24.                 Case 4 To 9
  25.                     sOutput = "th"
  26.             End Select
  27.     End Select
  28.     IntToOrdinalString = CStr(MyNumber) & sOutput
  29. End Function
Nov 24 '17 #2

Narender Sagar
100+
P: 187
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: 872
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
  1. Function OrdinalSuffix(ByVal Num As Long) As String
  2.         Dim N As Long
  3.         Const cSfx = "stndrdthththththth" ' 2 char suffixes
  4.         N = Num Mod 100
  5.         If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
  6.                 Or ((Abs(N) Mod 10) = 0) Then
  7.             OrdinalSuffix = "th"
  8.         Else
  9.             OrdinalSuffix = Mid(cSfx, _
  10.                 ((Abs(N) Mod 10) * 2) - 1, 2)
  11.         End If
  12.     End Function
  13.  
Dec 5 '17 #4

NeoPa
Expert Mod 15k+
P: 30,549
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
  1. Private Const conSuffix As String = "stndrdthththththth"
  2.  
  3. Public Function OrdinalSuffix(ByVal lngVal As Long) As String
  4.     lngVal = Abs(lngVal) Mod 100
  5.     If (lngVal = 0) _
  6.     Or (lngVal Between 4 And 20) Then
  7.         OrdinalSuffix = "th"
  8.     Else
  9.         OrdinalSuffix = Mid(conSuffix, (lngVal Mod 10) * 2 - 1, 2)
  10.     End If
  11. End Function
Dec 6 '17 #5

Expert 100+
P: 872
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

NeoPa
Expert Mod 15k+
P: 30,549
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

Post your reply

Sign in to post your reply or Sign up for a free account.