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,
Printing dates in a report
-
- Administrator
- Posts: 78537
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Printing dates in a report
You could add this function to the module that contains DateOrdinalEnding:
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.
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
=DateOrdinalEnding2([BirthDate], "mmmm")
where BirthDate is the birthdate field.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 26 Jul 2011, 14:08
Re: Printing dates in a report
Thank you -- this was the change I was looking for.