Format numbers "st", "nd", "rd", etc.

[Wayne-I-M]

Hi

Before I start writing a little public module to do this has anyone done a
numbers end (sorry dont know the right word) before and got any tips
"th"
"nd"
"rd"

Or (better still) - Is there an access "format" that will add these

Format(Date(),"something here")

Can't just get the last digit and run it as 1 and 11 are different (1"st"
and 11"th")

Many thanks

 

[Wayne-I-M]

It OK

just found this which works fine for dates and I have change it to work with
numbers.


Public Function NumSuffix(MyNum As Variant) As String
Dim n As Integer
Dim x As Integer
Dim strSuf As String
n = Right(MyNum, 2)
x = n Mod 10
strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", _
n <> 13 And x = 3, "rd", True, "th")
NumSuffix = LTrim(str(MyNum)) & strSuf
End Function
Public Function DateSay(ByRef pDte As Date) As String
Dim strHold As String
strHold = Format(pDte, "mmmm") & " " & NumSuffix(day(pDte)) & " " &
Format(pDte, "yyyy")
DateSay = strHold
End Function

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Ordinals.mdb" which illustrates how to do this. Yours is a
little tighter than mine. I use a CASE statement, which I believe is a
little more readable. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=305 if you're
interested in comparing.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Wayne-I-M]

Hi Roger

Just had a look at you applcation and it is really good. Thank you so much
I have gain much knowledge

Thank you
--
Wayne
Manchester, England.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Ordinals.mdb" which illustrates how to do this. Yours is a
little tighter than mine. I use a CASE statement, which I believe is a
little more readable. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=305 if you're
interested in comparing.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[James A. Fortune]

Hi

Before I start writing a little public module to do this has anyone done a
numbers end (sorry dont know the right word) before and got any tips
"th"
"nd"
"rd"

Or (better still) - Is there an access "format" that will add these

Format(Date(),"something here")

Can't just get the last digit and run it as 1 and 11 are different (1"st"
and 11"th")

Many thanks

Here's what I use:

Public Function AppendOrdinal(lngX As Long) As String
Dim intLastDigit As Integer

AppendOrdinal = ""
intLastDigit = Val(Right(CStr(lngX), 1))
Select Case intLastDigit
Case 1: AppendOrdinal = "st"
Case 2: AppendOrdinal = "nd"
Case 3: AppendOrdinal = "rd"
Case 0, 4 To 9: AppendOrdinal = "th"
End Select
If CLng(Right(CStr(lngX), 2)) >= 11 And _
CLng(Right(CStr(lngX), 2)) <= 13 Then AppendOrdinal = "th"
End Function

James A. Fortune
(e-mail address removed)