Microsoft Access VBA Date Format 1st 2nd 3rd 4th

VBA create the date format for the st part of 1st


Sub usage()
'
' The Excel way ...
' =TEXT(A1,"dddd, mmmm d") & LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})
'
Dim MyDate As Date
 MyDate = #11/27/2015#
 MsgBox Format(MyDate, "dddd ") & udfDate1st2nd3rd(Format(MyDate, "d"))
End Sub

Public Function udfDate1st2nd3rd(sDayNumber As String) As String
 udfDate1st2nd3rd = "   "
 If sDayNumber = "1" Then udfDate1st2nd3rd = "1st"
 If sDayNumber = "2" Then udfDate1st2nd3rd = "2nd"
 If sDayNumber = "3" Then udfDate1st2nd3rd = "3rd"
 If sDayNumber = "4" Then udfDate1st2nd3rd = "4th"
 If sDayNumber = "5" Then udfDate1st2nd3rd = "5th"
 If sDayNumber = "6" Then udfDate1st2nd3rd = "6th"
 If sDayNumber = "7" Then udfDate1st2nd3rd = "7th"
 If sDayNumber = "8" Then udfDate1st2nd3rd = "8th"
 If sDayNumber = "9" Then udfDate1st2nd3rd = "9th"
 If sDayNumber = "10" Then udfDate1st2nd3rd = "10th"
 If sDayNumber = "11" Then udfDate1st2nd3rd = "11th"
 If sDayNumber = "12" Then udfDate1st2nd3rd = "12th"
 If sDayNumber = "13" Then udfDate1st2nd3rd = "13th"
 If sDayNumber = "14" Then udfDate1st2nd3rd = "14th"
 If sDayNumber = "15" Then udfDate1st2nd3rd = "15th"
 If sDayNumber = "16" Then udfDate1st2nd3rd = "16th"
 If sDayNumber = "17" Then udfDate1st2nd3rd = "17th"
 If sDayNumber = "18" Then udfDate1st2nd3rd = "18th"
 If sDayNumber = "19" Then udfDate1st2nd3rd = "19th"
 If sDayNumber = "20" Then udfDate1st2nd3rd = "20th"
 If sDayNumber = "21" Then udfDate1st2nd3rd = "21st"
 If sDayNumber = "22" Then udfDate1st2nd3rd = "22nd"
 If sDayNumber = "23" Then udfDate1st2nd3rd = "23rd"
 If sDayNumber = "24" Then udfDate1st2nd3rd = "24th"
 If sDayNumber = "25" Then udfDate1st2nd3rd = "25th"
 If sDayNumber = "26" Then udfDate1st2nd3rd = "26th"
 If sDayNumber = "27" Then udfDate1st2nd3rd = "27th"
 If sDayNumber = "28" Then udfDate1st2nd3rd = "28th"
 If sDayNumber = "29" Then udfDate1st2nd3rd = "29th"
 If sDayNumber = "30" Then udfDate1st2nd3rd = "30th"
 If sDayNumber = "31" Then udfDate1st2nd3rd = "31st"
End Function


Example - Saturday 21st August 2010




Here is the simplest, easy to understand, way of doing this coded as a VBA user defined function.

Thanks to Ron Coderre Microsoft Excel MVP for the idea and the Excel code

Date format of just "st" "nd" "rd" and "th"


.