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

Ordinal Date

8 views
Skip to first unread message

Rachel

unread,
Oct 15, 2003, 1:25:16 PM10/15/03
to
Hi I have a database that stores information on people
including the date that we recieved the person's report. I
am trying to get the date to read in the report like on
the 17th day of September 2003. How do I format my date
field to read like that??? THANKS!!!!

Fredg

unread,
Oct 15, 2003, 2:47:53 PM10/15/03
to
Copy and Paste this into a module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' MoIn determines Month Format, i.e. "Feb" or "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")

DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & " yyyy")

End Function
=======================
Then you can call it from a query:
Exp:DateOrdinalEnding([DateField],"mmmm")

or directly as control source of an unbound control in a report:
=DateOrdinalEnding([DateField],"mmmm")

Hope this helps.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


"Rachel" <kpo...@wycokck.org> wrote in message
news:0de901c39341$4cf30c60$a001...@phx.gbl...

Rachel

unread,
Oct 15, 2003, 4:05:33 PM10/15/03
to
THANK YOU SO VERY MUCH!!!!!

Worked like a charm! :-)

>.
>

0 new messages