How do I add a suffix to a date in a report

[solarmaze]

When printing a report (that will actually be filling the blanks on a pre-printed award certificate), I need to have the date printed in a particular way.
Example:
If the date is July 2, 2003
The certificate would read:
On this the 2nd day of July in the year 2003…..
I know how to set the proper format in the properties for the date related text boxes in order to just display the day, month, or year…
But how can I add the appropriate day suffix, ie: st nd rd th to the day number?

I assume I’ll have to add some VB code somewhere, somehow, but I have no idea where or how.

I’m new at this, please really spell out the answer or direct me to the proper help file.

Thanks in advance

bill@solarmaze.com

 

[CosmoKramer]

Add this function to a new module:

Public Function fnFormattedDate(D1 As Date) As String
Select Case Day(D1)
  Case 1, 21, 31
    fnFormattedDate = Day(D1) & "st day of " & Format(D1, "mmmm") & " in the year " & Format(D1, "yyyy")
  Case 2, 22
    fnFormattedDate = Day(D1) & "nd day of " & Format(D1, "mmmm") & " in the year " & Format(D1, "yyyy")
  Case 3, 23
    fnFormattedDate = Day(D1) & "rd day of " & Format(D1, "mmmm") & " in the year " & Format(D1, "yyyy")
  Case Else
    fnFormattedDate = Day(D1) & "th day of " & Format(D1, "mmmm") & " in the year " & Format(D1, "yyyy")
End Select
End Function

Then, call this function with an expression field in your query:

FormattedDate: fnFormattedDate(YourDateFieldHere)

Hope this helps.....

 

[Norris68]

The easiest way would be to create a public function (in a module) as ...

Public Function CertHeader(CertDate As Date) As String
Dim D As Integer
Dim DaySuffix As String
D = Day(CertDate) Mod 10
Select Case D
Case 1: DaySuffix = "st"
Case 2: DaySuffix = "nd"
Case 3: DaySuffix = "rd"
Case Else
DaySuffix = "th"
End Select
CertHeader = "On this the " & Day(CertDate) & DaySuffix & _
" day of " & MonthName(Month(CertDate)) & _
" in the year " & Year(CertDate)
End Function

Then, create a textbox on your report and set the data property to "=CertHeader([MyDateField])"