Ordinal Date

[Rachel]

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]

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:
ExpateOrdinalEnding([DateField],"mmmm")

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

Hope this helps.

 

[Rachel]

THANK YOU SO VERY MUCH!!!!!

Worked like a charm!

-----Original Message-----
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:
ExpateOrdinalEnding([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.

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!!!!

.