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 |

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

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

.