UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date As Words, Any Version    
 
   
CyberCow
post Feb 12 2008, 06:20 AM
Post#1


UdderAccess Admin + UA Ruler
Posts: 19,551
Joined: 27-April 02
From: Upper MI


To get from 1/4/2009 to "January 4th, 2009" or "4th of January, 2009" or even simply "January 4th" just got a little simpler. This date slicer/dicer takes a valid date and turns it into verbose syntax.
special thanks to UA VIP jwhite for pointing out an error with variants of 11, 12 & 13 not getting their "th" - now corrected - March 13, 2011
CODE
Public Function DateAsWords(dt As Date, Optional f As Integer = 0)
' Usage: DateAsWords(#1/4/2009#) or DateAsWords(#1/4/2009#,1)
' dt = a valid date  -   'f' selects a format:
'     DateAsWords(#1/4/2009#)  = "4th"
'     DateAsWords(#1/4/2009#,1) = "January 4th, 2009"
'     DateAsWords(#1/4/2009#,2) = "4th of January, 2009"
'     DateAsWords(#1/4/2009#,3) = "4th of January"
' Make sure your date value is wrapped in #'s or is a valid date variable
' oterwise, you'll just get "1 divided by 4 divided by 2009" (1.24e-4)
' So why do simple numbers, (not even formatted like a date), return a result? . . . .
'
' Access treats dates as 8 byte numbers and balances 12/30/1899
' as the zero point. (Dunno why) If you date format zero
' Format(0,"m/d/yyyy") = 12/30/1899.
' However, if you inadvertantly use only 3 'y's
' you will get: Format(0,"m/d/yyy") = [URL="tel:12/30/99364"]12/30/99364[/URL]
' So, today's numeric date value is basically the number of days
' since 12/30/1899.
' The formula works negatively too. If you Format(-179,"m/d/yyyy")
' you will get 7/4/1899
' -657434 is the negative limit (1/1/100) and 2958465 is the upper
' limit ([URL="tel:12/31/9999"]12/31/9999[/URL])
    Select Case right(Day(dt), 1)
        Case 1
            If Day(dt) > 9 And Day(dt) < 14 Then
                DateAsWords = Day(dt) & "th"
              Else
                DateAsWords = Day(dt) & "st"
            End If
        Case 2
            If Day(dt) > 9 And Day(dt) < 14 Then
                DateAsWords = Day(dt) & "th"
              Else
                DateAsWords = Day(dt) & "nd"
            End If
        Case 3
            If Day(dt) > 9 And Day(dt) < 14 Then
                DateAsWords = Day(dt) & "th"
              Else
                DateAsWords = Day(dt) & "rd"
            End If
        Case Else
            DateAsWords = Day(dt) & "th"
    End Select
    
    Select Case f
        Case 0
            ' Change nothing
        Case 1
            DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)
        Case 2
            DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)
        Case 3
            DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")
    End Select
End Function
Go to the top of the page
 
2DanTodd
post Apr 30 2013, 01:56 PM
Post#2



Posts: 1
Joined: 30-April 13



Great code. Works in Access 2010 too, but while "DateAsWords(Date(),1)" gave me "April 30th, 2013", I had to use "DateAsWords(Date(),0)" to get "30th"
Go to the top of the page
 
CyberCow
post Apr 30 2013, 04:36 PM
Post#3


UdderAccess Admin + UA Ruler
Posts: 19,551
Joined: 27-April 02
From: Upper MI


Actually, "DateAsWords(Date(),1)" does deliver as advertised, rendering 'month dayxx, year'
sing 0 (instead of the 1) yields the same result as "DateAsWords(Date())" - because the numeric value defaults to zero (note the Optional "f" as Integer = 0 in the call line). So, you could leave the optional f value out to default to zero OR manually add the zero to get just the "dayxx" Either will work.
Glad you like it! big_grin.gif
Go to the top of the page
 
CharlesWilliams
post May 14 2015, 10:54 AM
Post#4



Posts: 124
Joined: 3-September 02
From: Philadelphia, PA


CyberCow,

I made a couple of tweaks to the DateAsWords code as follows:

Dim tm As String

Select Case Right(Day(dt), 1)
Case 1
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "st"
End If
Case 2
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "st"
End If
Case 3
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "nd"
End If
Case 4
If Day(dt) > 9 And Day(dt) < 14 Then
DateAsWords = Day(dt) & "th"
Else
DateAsWords = Day(dt) & "rd"
End If
Case Else
DateAsWords = Day(dt) & "th"
End Select

Select Case f
Case 0
' Change nothing
Case 1
DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)
Case 2
DateAsWords = Format(dt, "dddd") & " " & Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)
Case 3
DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)
Case 4
DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")
End Select

In my tweaks I made case 2 case 3 and case 3 case 4. I made case 2 and added the day of the week in the beginning. (Re: Thursday May 14th, 2015) for those who would want to show the day of the week before the date.

This is just my personal tweak.

- Charles
Go to the top of the page
 
ZapDude
post Jun 15 2017, 12:53 PM
Post#5



Posts: 282
Joined: 27-February 06
From: Long Beach CA


Actually, ordinal return is a rather simple matter. I reduced it to a single line of code using a Switch function, though for performance conditions you may wish to use Select Case or If conditionals. You can drop the function into your bag of tricks, or just use the single line code within your own procedures.

CODE
Public Function fncOrdinal(ByVal lngNumber As Long) As String

   fncOrdinal = CStr(lngNumber) & Switch( _
      lngNumber Mod 100 >= 11 And lngNumber Mod 100 <= 13, "th", _
      lngNumber Mod 10 = 1, "st", _
      lngNumber Mod 10 = 2, "nd", _
      lngNumber Mod 10 = 3, "rd", _
      -1, "th")

End Function
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th July 2017 - 03:53 AM