Convert Date Field Month and Day to Text

[LeBaron via AccessMonster.com]

I have a date field on my form, and i want the date field to spell out the
actual number and month of the date. ie..September would be spelled in
number form as "Ninth", and day (11) spelled out as "Eleventh". Can someone
guide me on what would be the code to write and where do i put the code to
get htis work please.

 

[Roger Carlson]

Your looking to create ordinals? Ordinals are ordered numbers: 1st, 2nd,
3rd, 4th,...etc. On my website (www.rogersaccesslibrary.com), is a small
Access database sample called "Ordinals.mdb" which illustrates how to do
this. This function shows how to add the correct suffix to any positive
integer. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=305

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

This function shows how to add the correct suffix to any positive integer.

 

[LeBaron via AccessMonster.com]

thanks, that shows the numbering system adding the "th" or "rd" or "st" to a
number, but i need it to spell out the number not just add those to the end
of the number. Plus also the date is coming from the date field which is
usually the system date from my input form. So i would need the number 11
spellout as "Eleventh" the actual word not number, and month, ie. 09 spellout
"Ninth".

 

[KARL DEWEY]

Why not just create two translation tables - Days and Months with two fields
- number & text?
Then you can use an IIF statements like this --
My_Field: IIF(Day([MyDate]) = Days.DayNum, Days.DayText, "Error") & " of "
& IIF(Month([MyDate]) = Months.MonNum, Months.MonText, "Error") & " month"

 

[LeBaron via AccessMonster.com]

Ok now where and how would i apply this IF statement. I have create two
tables name: Days- fields(Day, Text) and Months - fields(Month, Text)...

Why not just create two translation tables - Days and Months with two fields
- number & text?
Then you can use an IIF statements like this --
My_Field: IIF(Day([MyDate]) = Days.DayNum, Days.DayText, "Error") & " of "
& IIF(Month([MyDate]) = Months.MonNum, Months.MonText, "Error") & " month"

thanks, that shows the numbering system adding the "th" or "rd" or "st" to a
number, but i need it to spell out the number not just add those to the end
of the number. Plus also the date is coming from the date field which is
usually the system date from my input form. So i would need the number 11
spellout as "Eleventh" the actual word not number, and month, ie. 09 spellout
"Ninth".

 

[LeBaron via AccessMonster.com]

I also created a query linking the two tables, now i have a text box on my
form that i want to use the IF statement with the queries so if the date is
11, from the query/table it would read "Eleventh" and/or if the month is
September (short date= 9) it reads from the query Ninth. What would be the
IF statement i should use to reference the query or some type of statement i
should use, and would i put it on the criteria of the query of the control
source of the text box?