Ordinal numbers

[Guest]

Anyone have a suggestion for easily formatting numbers as ordinals in an
Access report?

Muy thanks.

 

[Rick B]

Are you saying you want line numbers in your report? Not sure what you are
asking for. Give us an example.



or·di·nal num·ber (plural or·di·nal num·bers)
noun

1. number showing order: a number used to show the relative position
of something or somebody in a sequence. "First" and "second" are ordinal
numbers.
Also called ordinal


2. mathematics logic size and order of a set: a measure of the size of
an ordered set in addition to the order of its elements

 

[Guest]

Formatting as Ordinal means if a value in a text box is 7 the value in the
report will read 7th; 11 would read 11th and 21 would read 21st

 

[Brendan Reynolds]

I have only briefly tested this, it seems to work, but I can't guarantee
that there are no exceptions that I haven't anticipated ...

Public Function ToOrdinal(ByVal TheNumber As Variant) As String

Dim strChar As String

If Len(TheNumber & vbNullString) > 0 Then
If Len(TheNumber) > 1 Then
If Mid$(TheNumber, Len(TheNumber) - 1, 1) = "1" Then
ToOrdinal = TheNumber & "th"
Else
Select Case Right$(TheNumber, 1)
Case "1"
ToOrdinal = TheNumber & "st"
Case "2"
ToOrdinal = TheNumber & "nd"
Case "3"
ToOrdinal = TheNumber & "rd"
Case Else
ToOrdinal = TheNumber & "th"
End Select
End If
Else
Select Case Right$(TheNumber, 1)
Case "1"
ToOrdinal = TheNumber & "st"
Case "2"
ToOrdinal = TheNumber & "nd"
Case "3"
ToOrdinal = TheNumber & "rd"
Case Else
ToOrdinal = TheNumber & "th"
End Select
End If
Else
ToOrdinal = vbNullString
End If

End Function

Here's an example of it's use in a query (in production, it might be better
to use it in the ControlSource of a text box on the report, but a query is a
good way of testing it with a range of different numbers) ...

SELECT tblTest.TestNumber, ToOrdinal([TestNumber]) AS TheOrdinal
FROM tblTest;

 

[Lynn Trapp]

This function should do what you want:

Function Ordinal_Numbers(P_Numeric As String) As String
Dim V_Ordinal As String
Dim V_Numeric As String
If Right(P_Numeric, 2) = "11" Or Right(P_Numeric, 2) = "12" Or
Right(P_Numeric, 2) = "13" Then
Ordinal_Numbers = "th"

Else
V_Numeric = Right(P_Numeric, 1)

Select Case V_Numeric
Case "1"
V_Ordinal = "st"
Case "2"
V_Ordinal = "nd"
Case "3"
V_Ordinal = "rd"
Case Else
V_Ordinal = "th"
End Select
Ordinal_Numbers = V_Ordinal
End If
End Function

Call the function from a query like this:

OrdinalNumber: [YourNumberField] & Ordinal_Numbers(CStr([YourNumberField]))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Pretty much what I thought. Your version is very tidy, Lynn. Many thanks.
Perhaps some future version of Access would think of this as a needed
Built-In Function.

This function should do what you want:

Function Ordinal_Numbers(P_Numeric As String) As String
Dim V_Ordinal As String
Dim V_Numeric As String
If Right(P_Numeric, 2) = "11" Or Right(P_Numeric, 2) = "12" Or
Right(P_Numeric, 2) = "13" Then
Ordinal_Numbers = "th"

Else
V_Numeric = Right(P_Numeric, 1)

Select Case V_Numeric
Case "1"
V_Ordinal = "st"
Case "2"
V_Ordinal = "nd"
Case "3"
V_Ordinal = "rd"
Case Else
V_Ordinal = "th"
End Select
Ordinal_Numbers = V_Ordinal
End If
End Function

Call the function from a query like this:

OrdinalNumber: [YourNumberField] & Ordinal_Numbers(CStr([YourNumberField]))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

Formatting as Ordinal means if a value in a text box is 7 the value in the
report will read 7th; 11 would read 11th and 21 would read 21st

 

[Lynn Trapp]

Pretty much what I thought. Your version is very tidy, Lynn. Many

thanks.
Perhaps some future version of Access would think of this as a needed
Built-In Function.

Lulu,
I'm glad that helps you. Brendan's version is a bit more robust than mine,
as it checks for a null entry in the number field. You could put this
function in a database and use it as an add-in for any and all of your
databases by setting a reference to it--almost as good as a Built-In.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html