Ordinal Date

  • Thread starter Thread starter Rachel
  • Start date Start date
R

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!!!!
 
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.
 
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:
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 said:
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!!!!
Click to expand...


.
Click to expand...
 
Back
Top