Date Format - display

[Guest]

I would like Access to display dates in the following format:
Wednesday 3rd August 2005

Currently, I can only get a display of :
Wednesday 3 August 2005 using dddd d mmmm yyyy

Grateful for help getting 3rd instead of 3 displayed.

fyi, Dave Peterson was kind enough to provide the solution below for Excel.

Many thanks

Stuart

 

[fredg]

I would like Access to display dates in the following format:
Wednesday 3rd August 2005

Currently, I can only get a display of :
Wednesday 3 August 2005 using dddd d mmmm yyyy

Grateful for help getting 3rd instead of 3 displayed.

fyi, Dave Peterson was kind enough to provide the solution below for Excel.

Many thanks

Stuart

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Tuesday 2nd August 2005
' MoIn determinesthe Month Format, i.e. "mmm" for "Aug" or "mmmm" for
"August"

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 = Format(DateIn, "dddd") & " " & dteX &
Format(DateIn, " " & MoIn & " yyyy")

End Function
====================

Call it from a query using:
Exp: DateOrdinalEnding([DateField], "mmmm")

Or from an unbound control in a report:
=DateOrdinalEnding([DateField],"mmmm")

 

[Douglas J Steele]

Format(DateField, "DDDD ") & Day(DateField) & _
IIf((Day(DateField) Mod 100 >= 10 And Day(DateField) Mod 100 <= 14), _
"th",Choose((Day(DateField) Mod 10) + 1, "th", "st", "nd", "rd", "th", "th",
"th", "th", "th", "th")) & _
Format(DateField, " MMMM YYYY")

 

[Guest]

Thanks Fred. This works perfectly.

Regards

Stuart

I would like Access to display dates in the following format:
Wednesday 3rd August 2005

Currently, I can only get a display of :
Wednesday 3 August 2005 using dddd d mmmm yyyy

Grateful for help getting 3rd instead of 3 displayed.

fyi, Dave Peterson was kind enough to provide the solution below for Excel.

Many thanks

Stuart

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Tuesday 2nd August 2005
' MoIn determinesthe Month Format, i.e. "mmm" for "Aug" or "mmmm" for
"August"

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 = Format(DateIn, "dddd") & " " & dteX &
Format(DateIn, " " & MoIn & " yyyy")

End Function
====================

Call it from a query using:
Exp: DateOrdinalEnding([DateField], "mmmm")

Or from an unbound control in a report:
=DateOrdinalEnding([DateField],"mmmm")