How to change date of birth into text

[Ayiramala]

Hi,
I have this date of birth field on table. For some reasons, I want to convert it into text. For example, 10/05/1978 should be converted into 'Tenth May Nineteen Seventy Eight'. Is there any way I can do this? Any help is truly appreciated.

[orange]

You would never store that in a table.
You could certainly create a user defined function to do this. You would only ever want to display this "strange format" on a form or report.
Can you tell us more about the " for some reasons"?

[June7]

I have seen need for converting number to words - like on checks but never seen need for date to words. Maybe this is for some sort of formal certificate.

This might give you idea of what is involved http://support.microsoft.com/kb/213360

[orange]

In addition to the link from June7, this code should get you started. You can work out the Year based on these posts.

Sub DatesToText()

    Dim MDate As Date
    Dim suffix As String
    MDate = #5/27/2014#
    Dim Ordinals(31)
    Ordinals(1) = "First"
    Ordinals(2) = "Second"
    Ordinals(3) = "Third"
    Ordinals(4) = "Fourth"
    Ordinals(5) = "Fifth"
    Ordinals(6) = "Sixth"
    Ordinals(7) = "Seventh"
    Ordinals(8) = "Eighth"
    Ordinals(9) = "Nineth"
    Ordinals(10) = "Tenth"
    Ordinals(11) = "Eleventh"
    Ordinals(12) = "Twelfth"
    Ordinals(13) = "Thirteenth"
    Ordinals(14) = "Fourteenth"
    Ordinals(15) = "Fifteenth"
    Ordinals(16) = "Sixteenth"
    Ordinals(17) = "Seventeenth"
    Ordinals(18) = "Eighteenth"
    Ordinals(19) = "Nineteenth"
    Ordinals(20) = "Twentieth"
    Ordinals(21) = "Twentyfirst"
    Ordinals(22) = "Twentysecond"
    Ordinals(23) = "Twentythird"
    Ordinals(24) = "Twentyfourth"
    Ordinals(25) = "Twentyfifth"
    Ordinals(26) = "Twentysixth"
    Ordinals(27) = "Twentyseventh"
    Ordinals(28) = "Twentyeighth"
    Ordinals(29) = "Twentynineth"
    Ordinals(30) = "Thirtieth"
    Ordinals(31) = "Thirtyfirst"

    If IsDate(MDate) Then
        Debug.Print Ordinals(Day(MDate)) & " " & MonthName(Month(MDate))
    End If


End Sub

[Ayiramala]

In addition to the link from June7, this code should get you started. You can work out the Year based on these posts.

Sub DatesToText()

    Dim MDate As Date
    Dim suffix As String
    MDate = #5/27/2014#
    Dim Ordinals(31)
    Ordinals(1) = "First"
    Ordinals(2) = "Second"
    Ordinals(3) = "Third"
    Ordinals(4) = "Fourth"
    Ordinals(5) = "Fifth"
    Ordinals(6) = "Sixth"
    Ordinals(7) = "Seventh"
    Ordinals(8) = "Eighth"
    Ordinals(9) = "Nineth"
    Ordinals(10) = "Tenth"
    Ordinals(11) = "Eleventh"
    Ordinals(12) = "Twelfth"
    Ordinals(13) = "Thirteenth"
    Ordinals(14) = "Fourteenth"
    Ordinals(15) = "Fifteenth"
    Ordinals(16) = "Sixteenth"
    Ordinals(17) = "Seventeenth"
    Ordinals(18) = "Eighteenth"
    Ordinals(19) = "Nineteenth"
    Ordinals(20) = "Twentieth"
    Ordinals(21) = "Twentyfirst"
    Ordinals(22) = "Twentysecond"
    Ordinals(23) = "Twentythird"
    Ordinals(24) = "Twentyfourth"
    Ordinals(25) = "Twentyfifth"
    Ordinals(26) = "Twentysixth"
    Ordinals(27) = "Twentyseventh"
    Ordinals(28) = "Twentyeighth"
    Ordinals(29) = "Twentynineth"
    Ordinals(30) = "Thirtieth"
    Ordinals(31) = "Thirtyfirst"

    If IsDate(MDate) Then
        Debug.Print Ordinals(Day(MDate)) & " " & MonthName(Month(MDate))
    End If


End Sub

That's very kind of you. But I am not very familiar with VB code, so could you please tell me where it should go on the report? Again, I will be grateful if you can show me just one example of the month and the year. Then I can work out the rest. Also, how do I put the three codes (for day, month and year) together?
Thank you very much.

[Ayiramala]

It is for printing a certificate. I don't know whether you do it like this, but in my country (India) we have such weird practices.

[June7]

The code would go in a VBA custom function procedure. Put code in a general module so it can be available from anywhere in the db. The code would extract each date part to convert to words and concatenate the word strings. Orange's code creates and populates an array for days of month. So something like:

Function DateToWords(dteDate As Date) As String

'insert orange's code to populate array for days

'adapt code from link to convert year part to words, include directly in this procedure or call function

DateToWords = Ordinals(Day(dteDate)) & " " & Format(dteDate, "mmmm") & " " & SpellNumber(Year(dteDate))

End Function


Call the function from textbox ControlSource, like: =DateToWords([datefield])

[Ayiramala]

The code would go in a VBA custom function procedure. Put code in a general module so it can be available from anywhere in the db. The code would extract each date part to convert to words and concatenate the word strings. Orange's code creates and populates an array for days of month. So something like:

Function DateToWords(dteDate As Date) As String

'insert orange's code to populate array for days

'adapt code from link to convert year part to words, include directly in this procedure or call function

DateToWords = Ordinals(Day(dteDate)) & " " & Format(dteDate, "mmmm") & " " & SpellNumber(Year(dteDate))

End Function


Call the function from textbox ControlSource, like: =DateToWords([datefield])

Sorry to bother you again, but is it like this:

Sub DatesToText()

Dim MDate As Date
Dim suffix As String
MDate = #5/27/2014#
Dim Ordinals(31)
Ordinals(1) = "First"
Ordinals(2) = "Second"
Ordinals(3) = "Third"
Ordinals(4) = "Fourth"
Ordinals(5) = "Fifth"
Ordinals(6) = "Sixth"
Ordinals(7) = "Seventh"
Ordinals(8) = "Eighth"
Ordinals(9) = "Nineth"
Ordinals(10) = "Tenth"
Ordinals(11) = "Eleventh"
Ordinals(12) = "Twelfth"
Ordinals(13) = "Thirteenth"
Ordinals(14) = "Fourteenth"
Ordinals(15) = "Fifteenth"
Ordinals(16) = "Sixteenth"
Ordinals(17) = "Seventeenth"
Ordinals(18) = "Eighteenth"
Ordinals(19) = "Nineteenth"
Ordinals(20) = "Twentieth"
Ordinals(21) = "Twentyfirst"
Ordinals(22) = "Twentysecond"
Ordinals(23) = "Twentythird"
Ordinals(24) = "Twentyfourth"
Ordinals(25) = "Twentyfifth"
Ordinals(26) = "Twentysixth"
Ordinals(27) = "Twentyseventh"
Ordinals(28) = "Twentyeighth"
Ordinals(29) = "Twentynineth"
Ordinals(30) = "Thirtieth"
Ordinals(31) = "Thirtyfirst"

If IsDate(MDate) Then
Debug.Print Ordinals(Day(MDate)) & " " & MonthName(Month(MDate))
End If


End Sub

Dim MDate As Date
Dim suffix As String
MDate = #5/27/2014#
Dim Ordinals(12)
Month(1) = "January"
Month(2) = "February"
Month(3) = "March"
Month(4) = "April"
Month(5) = "May"
Month(6) = "June"
Month(7) = "July"
Month(8) = "August"
Month(9) = "September"
Month(10) = "October"
Month(11) = "November"
Month(12) = "December"

If IsDate(MDate) Then
Debug.Print Ordinals(Day(MDate)) & " " & MonthName(Month(MDate))
End If


End Sub

Dim MDate As Date
Dim suffix As String
MDate = #5/27/2014#
Dim Ordinals(31)
Year(1995) = "Nineteen Ninety Five"
Year(1996) = "Nineteen Ninety Six"
Year(1997) = "Nineteen Ninety Seven"
Year(1998) = "Nineteen Ninety Eight"
Year(1999) = "Nineteen Ninety Nine"
Year(2000) = "Two Thousand"

If IsDate(MDate) Then
Debug.Print Ordinals(Day(MDate)) & " " & MonthName(Month(MDate))
End If


End Sub


Function DateToWords(dteDate As Date) As String

'insert orange's code to populate array for days

'adapt code from link to convert year part to words, include directly in this procedure or call function

DateToWords = Ordinals(Day(dteDate)) & " " & Format(dteDate, "mmmm") & " " & SpellNumber(Year(dteDate))

End Function

Should I write the code like this?Thanks.

[June7]

No, that is not what I instructed. Did you try the code you posted? It should error. There are 3 End Sub lines but only one Sub declaration.

You will be closer if you follow the instructional statements within the function I suggested:

'insert orange's code to populate array for days

'adapt code from link to convert year part to words, include directly in this procedure or call function

An array for months is not necessary. The Format function will extract the month name.

Could use an array for years instead of the SpellNumber function but your array stops at 2000. Need to build code to handle more years (how far - 2050?) or modify the code every year. If you use an array for the year then it would replace the SpellNumber function call in the concatenation.

If you use two array objects (one for days and one for years), they need to have different names.

You might want to learn more about arrays http://office.microsoft.com/en-us/ac...080206268.aspx

Don't really need the Debug statements. Orange just used that as a way to show the output of suggested code.

[Ayiramala]

I am really sorry to say that I can't do it... All that I was able to do was to get the month name right. Both day and year failed. It will be a great help for me if you could show me the beginning and ending of the code. And where the following should go:
Function DateToWords(dteDate As Date) As String

'insert orange's code to populate array for days

'adapt code from link to convert year part to words, include directly in this procedure or call function

DateToWords = Ordinals(Day(dteDate)) & " " & Format(dteDate, "mmmm") & " " & SpellNumber(Year(dteDate))

End Function

Does it belong to the same module?

Will that be asking too much? Many thanks again.

[Ayiramala]

May I ask one more thing? Can we do this with three tables- one for day, one for month, and the third for year - each with their own day, month and year strings? Just a thought, that's all.

[June7]

All the code should be in the same module. A general module would allow it to be called from anywhere. Here is the function I suggested with my instructions followed:

Function DateToWords(dteDate As Date) As String

  'insert orange's code to populate array for days
    Dim Ordinals(31)
    Ordinals(1) = "First"
    Ordinals(2) = "Second"
    Ordinals(3) = "Third"
    Ordinals(4) = "Fourth"
    Ordinals(5) = "Fifth"
    Ordinals(6) = "Sixth"
    Ordinals(7) = "Seventh"
    Ordinals(8) = "Eighth"
    Ordinals(9) = "Nineth"
    Ordinals(10) = "Tenth"
    Ordinals(11) = "Eleventh"
    Ordinals(12) = "Twelfth"
    Ordinals(13) = "Thirteenth"
    Ordinals(14) = "Fourteenth"
    Ordinals(15) = "Fifteenth"
    Ordinals(16) = "Sixteenth"
    Ordinals(17) = "Seventeenth"
    Ordinals(18) = "Eighteenth"
    Ordinals(19) = "Nineteenth"
    Ordinals(20) = "Twentieth"
    Ordinals(21) = "Twentyfirst"
    Ordinals(22) = "Twentysecond"
    Ordinals(23) = "Twentythird"
    Ordinals(24) = "Twentyfourth"
    Ordinals(25) = "Twentyfifth"
    Ordinals(26) = "Twentysixth"
    Ordinals(27) = "Twentyseventh"
    Ordinals(28) = "Twentyeighth"
    Ordinals(29) = "Twentynineth"
    Ordinals(30) = "Thirtieth"
    Ordinals(31) = "Thirtyfirst"

  'adapt code from link to convert year part to words, include directly in this procedure or call function

  DateToWords = Ordinals(Day(dteDate)) & " " & Format(dteDate, "mmmm") & " " & SpellNumber(Year(dteDate))

  End Function


'copy/paste code for the 4 functions from the link

Yes, can use tables as sources for the text values and use DLookup() expressions in textbox and eliminate all VBA.

=DLookup("DayText", "Days", "DayNum=" & Day([datefield])) & " " & Format(dteDate, "mmmm") & " " & DLookup("YearText", "Years", "YearNum=" & Year([datefield]))

Or even build queries that join the Days and Years tables to the data table.

[Ayiramala]

Thanks. Your help and patience is truly appreciated.
This time I was able to get both day and month right. But not year. Could you please show one example of the code for the year part? I tried several times, but each time it ends with an error.

[Ayiramala]

Success! Got it at last!
Many thanks to orange and June7. You've been very helpful.