Adding "st", "nd", "rd" to Numbers in Report

[mjseim]

This is more of a curiosity than anything else.

I was trying to figure out a way to convert numbers to text strings for various reporting purposes; example below:
1 = One, 1st, First
2 = Two, 2nd, Second
3 = Three, 3rd, Third
etc...

It didn't take long for me to decide to bag this challenge and simply create a table with this information (luckily, I only needed these conversions up to #30) with which I could join to my existing data.

Does anyone have a better way to do this? Is there some formula I'm not familiar with that can do this conversion on-the-fly?

Who knows, perhaps this would make for an interesting challenge.

 

[Rabbie]

I think you have the simplest way of doing this. A simple look up will be much quicker than trying to work it out on the fly asthere are so many paradoxical values to take account of. Ie number ending in 1 take st unless its eleven when its th. etc.

 

[mjseim]

Thanks for the reply. You're probably right... it's still a pretty neat challenge though.

 

[MStCyr]

Hi

As requested, here's a function that will do the job for you.


Function fCardinalDate(bytDay As Byte) As String
Select Case bytDay
Case 1, 21, 31
fCardinalDate = bytDay & "st"
Case 2, 22
fCardinalDate = bytDay & "nd"
Case 3, 23
fCardinalDate = bytDay & "rd"
Case Else
fCardinalDate = bytDay & "th"
End Select

End Function