Printing dates in a report

[mouisset1]

I have an existing database that is used to generate letters for employee birthday's. I want to change the format for the date so it reads in the month/day format vs X day of month format (November 3rd vs. 3rd day of November). I have two different fields in the query that I could pull this information from. One is the actual birthdate field, i.e. 3-Nov-65. The other is two expression fields that extract just the date and the month into seperate fields, i.e. 3, 11. Which field do I use, the actual date or the expressions? How do I get them to show in the format I want on the report?

Below is the current Ordinal Date command that is already in the report showing the date as 3rd day of Novmeber.

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 & " ")
End Function

Thank you,

[HansV]

You could add this function to the module that contains DateOrdinalEnding:

Code: Select all

Public Function DateOrdinalEnding2(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
        DateOrdinalEnding2 = ""
        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")
    DateOrdinalEnding2 = Format(DateIn, " " & MoIn & " ") & dteX
End Function

Use it just like the original function, for example in the control source of a text box on the report:

=DateOrdinalEnding2([BirthDate], "mmmm")

where BirthDate is the birthdate field.

[mouisset1]

Thank you -- this was the change I was looking for.