Need Your Help! How to Convert Date of Birth from Digits to Words (Text) Ms Access?

%3CLINGO-SUB%20id%3D%22lingo-sub-1562136%22%20slang%3D%22en-US%22%3ENeed%20Your%20Help!%20How%20to%20Convert%20Date%20of%20Birth%20from%20Digits%20to%20Words%20(Text)%20Ms%20Access%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3Ei%20am%20beginner%20dont%20understand%20coding%20very%20well%2C%20i%20saw%20a%20post%20in%20which%20i%20get%20code%20but%20i%20dont%20know%20it%20will%20work%20or%20now%20and%20i%20dont%20know%20how%20to%20use%20it%20in%20VBA.%20can%20anybody%20teach%20me%20%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ELike%201%2F1%2F2000%20First%20January%20of%20Two%20Thousand%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EOption%20Compare%20Database%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EOption%20Explicit%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESub%20Test()%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20TestDate%20As%20Date%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ETestDate%20%3D%20CDate(%2211.04.2013%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20dtString%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EdtString%20%3D%20DateInWords(TestDate)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMsgBox%20(dtString)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EFunction%20DateInWords(dt%20As%20Date)%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strDays%20As%20String%2C%20SplitDays%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrDays%20%3D%20%22Day_1%2CDay_2%2CDay_3%2CDay_4%2CDay_5%2CDay_6%2CDay_7%2CDay%20_8%2CD%20ay_9%2CDay_10%2CDay_11%2CDay_12%2CDay_13%2CDay_14%2CDay_15%2CDay%20_16%2CDay_17%2CDay_18%2CDay_19%2CDay_20%2CDay_21%2CDay_22%2CDay_%2023%2CDay_24%2CDay_25%2CDay_26%2CDay_27%2CDay_28%2CDay_29%2CDay_3%200%2CDay_31%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESplitDays%20%3D%20Split(strDays%2C%20%22%2C%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strMothes%20As%20String%2C%20SplitMonthes%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrMothes%20%3D%20%22Month_1%2CMonth_2%2CMonth_3%2CMonth_4%2CMonth_5%2CMonth%20_6%2CM%20onth_7%2CMonth_8%2CMonth_9%2CMonth_10%2CMonth_11%2CMonth_12%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESplitMonthes%20%3D%20Split(strMothes%2C%20%22%2C%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strYears%20As%20String%2C%20SplitYears%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrYears%20%3D%20%22Year_2010%2CYear_2011%2CYear_2012%2CYear_2013%2CYear_%202014%20%2CYear_2015%2CYear_2016%2CYear_2017%2CYear_2018%2CYear_2019%20%2CYear_2020%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESplitYears%20%3D%20Split(strYears%2C%20%22%2C%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strDay%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrDay%20%3D%20SplitDays(Day(dt)%20-%201)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strMonth%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrMonth%20%3D%20SplitMonthes(Month(dt)%20-%201)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDim%20strYear%20As%20String%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EstrYear%20%3D%20SplitYears(Year(dt)%20-%202010)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDateInWords%20%3D%20strDay%20%26amp%3B%20%22%20%22%20%26amp%3B%20strMonth%20%26amp%3B%20%22%2C%20%22%20%26amp%3B%20strYear%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Function%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1562136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eage%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDate%20of%20Birth%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIn%20English%20Words%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562539%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Your%20Help!%20How%20to%20Convert%20Date%20of%20Birth%20from%20Digits%20to%20Words%20(Text)%20Ms%20Access%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562539%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20function%20will%20not%20provide%20you%20with%20the%20result%20you%20desire.%26nbsp%3B%20For%20instance%2C%20using%20the%20Test%20sub%2C%20it%20returns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDay_4%20Month_11%2C%20Year_2013%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20suggest%20you%20look%20at%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4694-excel-date-to-words.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4694-excel-date-to-words.html%3C%2FA%3E%20as%20it%20will%20give%20you%20the%20format%20you%20are%20seeking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562585%22%20slang%3D%22de-DE%22%3ESubject%3A%20Need%20Your%20Help!%20How%20to%20Convert%20Date%20of%20Birth%20from%20Digits%20to%20Words%20(Text)%20Ms%20Access%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562585%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F551570%22%20target%3D%22_blank%22%3E%40ZKMPk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20sure%20since%20the%20translation%20isn't%20the%20best%2C%20so%20maybe%20you're%20looking%20for%20something%20like%20that.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20OnlyText()%0ADim%20lngRow%20As%20Long%0AFor%20lngRow%20%3D%201%20To%20Cells(Rows.Count%2C%204).End(xlUp).Row%0AIf%20Not%20IsEmpty(Cells(lngRow%2C%204))%20Then%0AIf%20IsDate(Cells(lngRow%2C%204))%20Then%0ACells(lngRow%2C%204)%20%3D%20Format(Cells(lngRow%2C%204)%2C%20%22DDDD.MMMM.YYYY%22)%0AEnd%20If%0AEnd%20If%0ANext%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562855%22%20slang%3D%22en-US%22%3EBetreff%3A%20Need%20Your%20Help!%20How%20to%20Convert%20Date%20of%20Birth%20from%20Digits%20to%20Words%20(Text)%20Ms%20Access%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562855%22%20slang%3D%22en-US%22%3EThat's%20Excel%20code%20and%20this%20is%20an%20Access%20question%2Fforum.%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20are%20no%20cells%2C%20rows%2C%20xlUp%2C%20...%20in%20Access.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562869%22%20slang%3D%22de-DE%22%3ESubject%3A%20Need%20Your%20Help!%20How%20to%20Convert%20Date%20of%20Birth%20from%20Digits%20to%20Words%20(Text)%20Ms%20Access%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562869%22%20slang%3D%22de-DE%22%3Eups%20sry%20wrong%20forum%20landed%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20but%20if%20I'm%20here%20...%20here%20is%20a%20link%20to%20help%20...%20if%20it%20helps%20%3A)%3C%2Fimg%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fcodekabinett.com%2Frdumps.php%3FLang%3D2%26amp%3BtargetDoc%3Ddate-time-data-type-vba-access%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcodekabinett.com%2Frdumps.php%3FLang%3D2%26amp%3BtargetDoc%3Ddate-time-data-type-vba-access%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20third-party%20products%20that%20these%20article%20discusses%20are%20manufactured%20by%20companies%20that%20are%20independent%20of%20me.%20I%20makes%20no%20warranty%2C%20implied%20or%20otherwise%2C%20about%20the%20performance%20or%20reliability%20of%20these%20products.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Thx%20%3CBR%20%2F%3E%20Nikolino%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

i am beginner dont understand coding very well, i saw a post in which i get code but i dont know it will work or now and i dont know how to use it in VBA. can anybody teach me ?
Like 1/1/2000 First January of Two Thousand

Option Compare Database
Option Explicit

Sub Test()
Dim TestDate As Date
TestDate = CDate("11.04.2013")
Dim dtString As String
dtString = DateInWords(TestDate)

MsgBox (dtString)
End Sub

Function DateInWords(dt As Date) As String
Dim strDays As String, SplitDays
strDays = "Day_1,Day_2,Day_3,Day_4,Day_5,Day_6,Day_7,Day _8,D ay_9,Day_10,Day_11,Day_12,Day_13,Day_14,Day_15,Day _16,Day_17,Day_18,Day_19,Day_20,Day_21,Day_22,Day_ 23,Day_24,Day_25,Day_26,Day_27,Day_28,Day_29,Day_3 0,Day_31"
SplitDays = Split(strDays, ",")
Dim strMothes As String, SplitMonthes
strMothes = "Month_1,Month_2,Month_3,Month_4,Month_5,Month _6,M onth_7,Month_8,Month_9,Month_10,Month_11,Month_12"
SplitMonthes = Split(strMothes, ",")
Dim strYears As String, SplitYears
strYears = "Year_2010,Year_2011,Year_2012,Year_2013,Year_ 2014 ,Year_2015,Year_2016,Year_2017,Year_2018,Year_2019 ,Year_2020"
SplitYears = Split(strYears, ",")

Dim strDay As String
strDay = SplitDays(Day(dt) - 1)
Dim strMonth As String
strMonth = SplitMonthes(Month(dt) - 1)
Dim strYear As String
strYear = SplitYears(Year(dt) - 2010)

DateInWords = strDay & " " & strMonth & ", " & strYear
End Function

4 Replies
Highlighted

That function will not provide you with the result you desire.  For instance, using the Test sub, it returns

 

Day_4 Month_11, Year_2013

 

I'd suggest you look at https://www.extendoffice.com/documents/excel/4694-excel-date-to-words.html as it will give you the format you are seeking.

 

Highlighted

@ZKMPk 

 

I'm not sure since the translation isn't the best, so maybe you're looking for something like that.

 

 

Sub OnlyText()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, 4).End(xlUp).Row
If Not IsEmpty(Cells(lngRow, 4)) Then
If IsDate(Cells(lngRow, 4)) Then
Cells(lngRow, 4) = Format(Cells(lngRow, 4), "DDDD.MMMM.YYYY")
End If
End If
Next
End Sub

 

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
That's Excel code and this is an Access question/forum.

There are no cells, rows, xlUp, ... in Access.
Highlighted
ups sry wrong forum landed

but if I'm here ... here is a link to help ... if it helps :)

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=date-time-data-type-vba-access

The third-party products that this article discusses are manufactured by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.

Thx
Nikolino