Format date with 'nd' or 'th' after the day number in Access

database, date, format, ms-access, ms-access-2007

Solution

Unfortunately the VBA `Format` function doesn't provide the capability you want. However you could use a custom VBA function to format the day part of the event date and add on the formatted month and year.

DayString([event date]) & " " & Format([event date], "mmmm, yyyy")

Save this function in a standard module.

Public Function DayString(ByVal pDate As Date) As String
    Dim intDay As Integer
    Dim strReturn As String

    intDay = Day(pDate)
    Select Case intDay
    Case 1, 21, 31
        strReturn = intDay & "st"
    Case 2, 22
        strReturn = intDay & "nd"
    Case 3, 23
        strReturn = intDay & "rd"
    Case Else
        strReturn = intDay & "th"
    End Select
    DayString = strReturn
End Function

Problem

I'm printing menu cards for events in an Access 2007 report want the event date at the bottom of the print out formatted in the following way: 2nd December, 2013 (or 25th December, 2013) Is there a way that I can format the date field in the Access database so that it prints out that way?

Original source