Ordinal numbers

B

Brian

I wish to to convert a criteria in Access Query to an ordinal number on a
report. Numbers less than 10 so I could use a protracted IIf sequence. I am
using Access 2003.
 
B

Brendan Reynolds

Here's something I posted in request to a similar question in another
newsgroup recently.

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;
 
Top