How do I change my datetime format on my report

[sandie]

I'm a beginner and I really need your help.

I have a datetime field on my table called Birthdate with Short Date
format which is 12/22/2007. On my report, I would like it to show
exactly like this:

on the 22nd day of December, 2007

Any help will be greatly appreciated.

Thank you.

 

[fredg]

I'm a beginner and I really need your help.

I have a datetime field on my table called Birthdate with Short Date
format which is 12/22/2007. On my report, I would like it to show
exactly like this:

on the 22nd day of December, 2007

Any help will be greatly appreciated.

Thank you.

Copy and Paste the following function into a module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' MoIn determines Month Format, i.e. "mmm" for "Feb" or "mmmm" for
"February"

If IsNull(DateIn) Then
DateOrdinalEnding = ""
Exit Function
End If

Dim dteX As String
dteX = DatePart("d", DateIn)

' The next is all on one line
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

' The next is all on one line
DateOrdinalEnding = "on the " & dteX & " day of" & Format(DateIn, " "
& MoIn & ", yyyy")

End Function

You can then call it from a query:

Exp: DateOrdinalEnding([Birthdate],"mmmm")

or directly in the control source of an unbound control:

= DateOrdinalEnding([Birthdate],"mmmm")

 

[Marshall Barton]

I'm a beginner and I really need your help.

I have a datetime field on my table called Birthdate with Short Date
format which is 12/22/2007. On my report, I would like it to show
exactly like this:

on the 22nd day of December, 2007

Set the text box to this kind of expression:

=Day(Birthdate) & IIf(Day(Birthdate)\10 = 1 Or
(Day(Birthdate)+6) Mod 10 < 7,"th", Choose(Day(Birthdate)
Mod 10, "st", "nd", "rd")) & " day of " &
Format(Birthdate,"mmmm, yyyy")

If you can do it, I suggest that you translate that logic to
a public Function in a standard VBA module and just call
the function from the text box.