How to Convert Date to "nth day of month, year"?

[bayoeth]

hi guys,



i'm new to this forum, and ms access.

I was making a certificate and made a table where it has a field containing a date like mm/dd/yyyy.
Then I made a form, but in that form I would like the date to appear as

nth day of the month, year.

For Example:
If date is December 3, 2017, It would appear as "3rd day of December, 2017"

I was trying to search the web, but i couldn't find any. Please help me. Thanks in advance.

[andy49]

Something like

Function datesentence (mydate as date) as string
'assuming mydate is the date you are dealing with
dim getmth as string
dim getday as integer
dim dateend as string
Getmth = month(mydate)
Getday =  DatePart("d",mydate)

Select case as getday

Case is = 1,21,31
Dateend="st"

Case is = 2,22
Dateend ="nd"

Case is = 3,23
Dateend ="rd"

Case else 
Dateend = "th"

End select

datesentence = getday & dateend & "day of " & Getmonth

[CJ_London]

or within a query you can use the format function

FormattedDate: Format([myDate],"d""" & Switch(Day([myDate]) Mod 10=1,"st",Day([myDate]) Mod 10=2,"nd",Day([myDate]) Mod 10=3,"rd",True,"th") & " day of ""mmmm\,yyyy")