Date Format

[Roger Bell]

I have a Text box in a report that looks as follows:

=Trim("I certifiy that the ashes of" & " " & [Full Name of Person to be
Interred] & " " & "formerly of" & " " & [Last Address] & " " & "were interred
in Plot #" & " " & [Section Name] & " " & "in the St Vincent's Catholic
Parish Memorial Garden on" & " " & Format([Date of Interment],"dddd, d mmmm
yyyy"))

Is it possible to alter the Format of the Date field so that it reads for eg:
Monday 22nd December 2008. At the moment it reads Monday 22 December 2008.
Can this format be altered to reflect the 22nd, 23rd, 1st etc, depending on
the actual day?
Thanks for any help?

 

[fredg]

I have a Text box in a report that looks as follows:

=Trim("I certifiy that the ashes of" & " " & [Full Name of Person to be
Interred] & " " & "formerly of" & " " & [Last Address] & " " & "were interred
in Plot #" & " " & [Section Name] & " " & "in the St Vincent's Catholic
Parish Memorial Garden on" & " " & Format([Date of Interment],"dddd, d mmmm
yyyy"))

Is it possible to alter the Format of the Date field so that it reads for eg:
Monday 22nd December 2008. At the moment it reads Monday 22 December 2008.
Can this format be altered to reflect the 22nd, 23rd, 1st etc, depending on
the actual day?
Thanks for any help?

Copy and paste the below function into a module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Novermber 13th, 2000
' 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)

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

' November 13th, 2000
' DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")
' **********************
' 17th day of September 2003'
'DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & "
yyyy")
'***********************
' Friday 4th of July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " of " &
Format(DateIn, " " & MoIn & ", yyyy")

' Friday 4th July, 2008 ****
DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************

' "Day of MONTH, Year" 4th of JULY, 2008
' DateOrdinalEnding = dteX & " of " & UCase(Format(DateIn, " " & MoIn
& ", yyyy"))

End Function

Then in your report:
=Trim("I certifiy that the ashes of " & [Full Name of Person to be
Interred] & " formerly of " & [Last Address] & " were interred in
Plot # " & [Section Name] & " in the St Vincent's Catholic Parish
Memorial Garden on " & DateOrdinalEnding([Date of Interment],"mmmm"))

Note above I've gotten rid of extraneous & " " concatenations for
you.

I've indicated the format you called for with 4 asterisks ****.
To use one of the other formats shown, simply comment out the current
code and un-comment that code.