View Full Version : Date Formatting


reptar
12-13-2008, 12:01 AM
Is there a way i can set the date to display e.g. 16th April?

I'm not sure if Access is capable of calculating "th" or "st" or "nd".

Any thoughts would be greatly appreciated.

Thankyou.

Atomic Shrimp
12-13-2008, 12:31 AM
I don't know if there's any built-in function for it - I suspect not, as the suffixes will be different for different languages.

You could do it by creating a table:
daynumber, suffix
1,st
2,nd
3,rd
4,th
...
30,th
31,st

Then create a custom function to pull out the day part of the date, look it up in the table and tack on the relevant suffix.

jwhite
12-13-2008, 09:01 AM
This will work nicely:
Function fCardinalDate(Optional dtmDate As Date = 0) As String
' Immediate Window Test: fCardinalDate(Date) returns - December 13th, 2008

Dim strTemp As String

'Setting to 0 sets Date to the beginning - 12/30/1899 00:00:00
If dtmDate = #12:00:00 AM# Then dtmDate = Date

Select Case Day(dtmDate)
Case 1, 21, 31: strTemp = "st"
Case 2, 22: strTemp = "nd"
Case 3, 23: strTemp = "rd"
Case Else: strTemp = "th"
End Select
fCardinalDate = Format(dtmDate, "mmmm") & " " & _
Day(dtmDate) & strTemp & ", " & _
Year(dtmDate)
End Function