Formatting the day of the month as an ordinal number

rbrule · Feb 9, 2006

Is there a way to format the day of the month as an ordinal number? For example instead of formatting the day as January 1, I would like to format the day as the 1st, 2nd, 3rd etc. day of the month. I need to do this in order to match the format of a contract that we want to use as a mail merge.

raskew · Feb 9, 2006

Hi -

See if this post http://www.access-programmers.co.uk/forums/showthread.php?t=98794 provides what you're after.

The link above doesn't want to cooperate, so here's what it would have provided.
Copy/paste to a new module then test from the debug (immediate) window with:

? datesay(#1/29/06#)

Code:

Function NumSuffix(MyNum As Variant) As String
'*******************************************
'Purpose:   Add suffix to a number
'Inputs:    ? NumSuffix(234)
'Output:    234th
'*******************************************

Dim n      As Integer
Dim x      As Integer
Dim strSuf As String

    n = Right(MyNum, 2)
    x = n Mod 10
    strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", _
                    n <> 13 And x = 3, "rd", True, "th")
    NumSuffix = LTrim(str(MyNum)) & strSuf

End Function

Public Function DateSay(ByRef pDte As Date) As String
Dim strHold As String

    strHold = Format(pDte, "mmmm") & " " & NumSuffix(day(pDte)) & " " & Format(pDte, "yyyy")
    DateSay = strHold
    
End Function

rbrule · Feb 11, 2006

Thank you for your help. I was able to solve my problem in the query that feeds the mail merge. I created another calculated field with an IIf statement that adds the suffixes. It was a simple solution, I don't know why I didn't think of it in the first place.

Thanks again for your help, I really appreciate it.

Ron

Formatting the day of the month as an ordinal number

rbrule

Registered User.

raskew

AWF VIP

rbrule

Registered User.

Similar threads

Users who are viewing this thread