Pull apart a Date

[Greg Ripper]

I am making a form that needs to fill in the following senetence...

On this the <DAY> day of <MONTH> A.D. 2004......

How can I get a report to just show the day and the month of a date that
I just pull in as today's date from a date field on a table. =date()

Thanks As Always,

Rip

 

[Roger Carlson]

Paste the following two procedures into a code module and run SplitDate:
'----------------------
Sub SplitDate()
dayz = strOrdinal(DatePart("d", Date))
monthz = DatePart("m", Date)
yearz = DatePart("yyyy", Date)

Select Case monthz
Case 1
monthzz = "January"
Case 2
monthzz = "February"
Case 3
monthzz = "March"
Case 4
monthzz = "April"
Case 5
monthzz = "May"
Case 6
monthzz = "June"
Case 7
monthzz = "July"
Case 8
monthzz = "August"
Case 9
monthzz = "September"
Case 10
monthzz = "October"
Case 11
monthzz = "November"
Case 12
monthzz = "December"
End Select

MsgBox "On this the " & dayz & " day of " & monthzz & " A.D. " & yearz

End Sub


Function strOrdinal(n) As String
If n < 0 Then
strOrdinal = "negative numbers not supported"
Exit Function
End If
Select Case n \ 10
Case 1
strOrdinal = n & "th"
Case Else
Select Case n Mod 10
Case 1
If n Mod 100 = 11 Then
strOrdinal = n & "th"
Else
strOrdinal = n & "st"
End If
Case 2
If n Mod 100 = 12 Then
strOrdinal = n & "th"
Else
strOrdinal = n & "nd"
End If
Case 3
If n Mod 100 = 13 Then
strOrdinal = n & "th"
Else
strOrdinal = n & "rd"
End If
Case Else
strOrdinal = n & "th"
End Select
End Select
End Function
'----------------------

I didn't bother with error trapping or declaring variables or passing in a
date. It just works on today's date, however, you should be able to add
these.

 

[Andrew Smith]

This should give you what you need:

"On this the " & Format(Date(),"dd") & _
numbersuffix(format(date(),"dd")) & _
" day of " & format(date(),"mmmm") & _
" A.D. " & format(date(),"yyyy")

It uses the following function:

Public Function NumberSuffix(varNo As Variant) As String

Select Case Right(CStr(varNo), 1)
Case "1"
NumberSuffix = "st"
Case "2"
NumberSuffix = "nd"
Case "3"
NumberSuffix = "rd"
Case Else
NumberSuffix = "th"
End Select

End Function

 

[Roger Carlson]

What about 11, 12, & 13?

 

[Andrew Smith]

Yes indeed! That occured to me just after I clicked the post button! You've
obviously provided a perfectly good solution to this, but to get my method
to work I've rewritten it as follows:

Public Function NumberSuffix(varNo As Variant) As String

Dim strNumber As String
Dim strSuffix As String

strNumber = CStr(varNo)

If Len(strNumber) > 1 Then
If Mid(strNumber, Len(strNumber) - 1, 1) = "1" Then
strSuffix = "th"
End If
End If

If Len(strSuffix) = 0 Then
Select Case Right(strNumber, 1)
Case "1"
strSuffix = "st"
Case "2"
strSuffix = "nd"
Case "3"
strSuffix = "rd"
Case Else
strSuffix = "th"
End Select
End If

NumberSuffix = strSuffix

End Function

 

[Roger Carlson]

Actually, I'm a tad embarrassed about using a Case to find the month. I
ripped the code from an ASP page I created several years ago that did the
same thing http://www.rogerjcarlson.com/poeticlicense/PoeticLicense.asp
and I didn't think about using Format instead.

 

[Andrew Smith]

Why use one line when twenty will do I've certainly done similar things
plenty of times.