Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

NEED TO PRINT 1ST INSTEAD OF 1

Status
Not open for further replies.

gearhead03

Technical User
Mar 29, 2003
147
US
I use a query to mail merge to a word document. I need to export 1st, 2nd 3rd, etc for the date rather than just 1, 2, 3. any help would be appreciated.

Mark A. Kale
 
Try a custom function...

Code:
Function DateSuffix(dateinfo As Date)
' dateinfo = date in question
Dim sufx As String
    Select Case DatePart("d", dateinfo)
    ' get the day from the date
        Case 1, 21, 31
            sufx = "st"
        Case 2, 22
            sufx = "nd"
        Case 3
            sufx = "rd"
        Case Else
            sufx = "th"
    End Select
    DateSuffix = Format(dateinfo, "mmmm d") & sufx & ", " & Year(dateinfo)
End Function

Tweak the last line for the exact format you want.

When calling the function, you may need to include a check for a valid date

Newdateformat = IIF(IsDate([datefield]),Datesuffix[datefield],"Date unknown")

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top