Solved how and where to call a function for ordinal numbers (1 Viewer)

Ihk

Member
Local time
Today, 12:00
Joined
Apr 7, 2020
Messages
280
Hi, I wan to convert my numbers into ordinal numbers like 1st, 2nd, 3rd etc on a form. I know this is very basic question, please dont mind, will be happy if you can help me. I have found a function, but not sure.
I want to call it for specefic control (numbered field) only on my form.
My form has ranking system, but button click record moves up and down, and the Rank / position field (numbered) changes for that row.
Question:
HOW and WHERE to call this function for that specific control which has the numbers (this is numbered field, it is not ID field).
Thanks in advance.
Below is the function
Code:
Function Addth(pNumber As String) As String
'UpdatebyExtendoffice20160628
Select Case CLng(VBA.Right(pNumber, 1))
    Case 1
    Addth = pNumber & "st"
    Case 2
    Addth = pNumber & "nd"
    Case 3
    Addth = pNumber & "rd"
    Case Else
    Addth = pNumber & "th"
End Select
Select Case VBA.CLng(VBA.Right(pNumber, 2))
    Case 11, 12, 13
    Addth = pNumber & "th"
End Select
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:00
Joined
Oct 29, 2018
Messages
21,358
Hi. It might be best if you could call the function in your query, so the result will become part of your form's data.
 

Ihk

Member
Local time
Today, 12:00
Joined
Apr 7, 2020
Messages
280
Hi. It might be best if you could call the function in your query, so the result will become part of your form's data.
Thank you but I am new to calling functions, How can I call this ?
 

plog

Banishment Pending
Local time
Today, 06:00
Joined
May 11, 2011
Messages
11,613
In a form you would set the control source to this:

=Addth(5)

And it would return "5th". You can also put a field inside the parenthesis and it would process that number per its rules:

=Addth([FieldName])

In a query its very similar:

OrdinalNumber: Addth([YourFieldName])

However, I'm pretty sure that thing fails for any number ending in 11, 12 or 13. It simply will never hit that portion of the code.
 
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 12:00
Joined
Apr 7, 2020
Messages
280
In a form you would set the control source to this:

=Addth(5)

And it would return "5th". You can also put a field inside the parenthesis and it would process that number per its rules:

=Addth([FieldName])

In a query its very similar:

OrdinalNumber: Addth([YourFieldName])

However, I'm pretty sure that thing fails for any number ending in 11, 12 or 13. It simply will never hit that portion of the code.
@plog !! I am grateful. Thanks a lot. It has worked I tested it upto 30th. All were correct.
 

Users who are viewing this thread

Top Bottom