Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: how do I add text dates

1 view
Skip to first unread message

James A. Fortune

unread,
Feb 21, 2006, 5:35:51 PM2/21/06
to
Terry wrote:
> I want to add text to the end of a date ie. 22nd, 23rd, 5th etc
>
> I am using Datepart(Now(), "dd") to extract the day.
>
> Can anyone tell be how to add the text that go at the end.
>
> Thanks
>

Because some numbers don't fit the pattern, perhaps use a function (air
code):

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

AppendOrdinal = ""
Select Case Right(CStr(lngX), 1)
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

It could be used like:
lngX = Datepart(Now(), "dd")
strOrdinal = lngX & AppendOrdinal(lngX)

or

Select Datepart(Now(), "dd") & AppendOrdinal(Datepart(Now(), "dd")) AS
DateDesc ...

Hopefully this works for numbers larger than 31 also. I'm not sure I
haven't missed any. I'm feeling too lazy to nest some IIf functions or
to use the switch() function.

James A. Fortune
MPAP...@FortuneJames.com

James A. Fortune

unread,
Feb 21, 2006, 5:43:47 PM2/21/06
to

BTW, I tried the function and it needs some TLC.

James A. Fortune
MPAP...@FortuneJames.com

fredg

unread,
Feb 21, 2006, 6:22:48 PM2/21/06
to
On Tue, 21 Feb 2006 13:59:27 -0800, Terry wrote:

> I want to add text to the end of a date ie. 22nd, 23rd, 5th etc
>
> I am using Datepart(Now(), "dd") to extract the day.
>
> Can anyone tell be how to add the text that go at the end.
>
> Thanks

Place this function in a new Module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date and display the month in Upper
Case.
' i.e. 13th Novermber 2000
' MoIn determines Month Format, i.e. "mmm" = "Feb"
' or "mmmm" = "February"

If IsNull(DateIn) Then
DateOrdinalEnding = ""
Exit Function
End If
Dim dteX As String
dteX = DatePart("d", DateIn)

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod
10, "st", "nd", "rd"), "th")

' show in "Day Month, Year" order "13th November 2000"
DateOrdinalEnding = dteX & UCase(Format(DateIn, " " & MoIn & " yyyy"))

End Function.
===

Call it from a control's control source using:
= DateOrdinalEnding([DateField],"mmmm")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

0 new messages